package com.zdkj.subway.check.controller.stationTiecketManager.xianWngCJXXQR.shouRuJS;

import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.zdkj.admin.api.entity.SysUser;
import com.zdkj.common.core.util.SpringContextHolder;
import com.zdkj.common.security.service.PigxUser;
import com.zdkj.common.security.util.SecurityUtils;
import com.zdkj.subway.check.entity.SwAuTvmAmountDefference;
import com.zdkj.subway.check.entity.SwAuTvmBoxAmountDifference;
import com.zdkj.subway.check.entity.SwStTvmCheckDetail;
import com.zdkj.subway.check.entity.SwStTvmIncomeSettle;
import com.zdkj.subway.check.mapper.SwAuTvmAmountDefferenceMapper;
import com.zdkj.subway.check.mapper.SwAuTvmBoxAmountDifferenceMapper;
import com.zdkj.subway.check.mapper.SwStTvmIncomeSettleMapper;
import com.zdkj.subway.check.mapper.SysUserMapper;
import com.zdkj.subway.check.util.DateFormat;
import com.zdkj.subway.check.util.JdbcUtil;
import com.zdkj.subway.check.util.UserInfo;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 *  tvm工具，主要是金额获取
 */
public class TvmSqlUpdate {
	
	private static final Logger LOGGER = LoggerFactory.getLogger(TvmSqlUpdate.class);

    private final static  String userName="管理员";
	private final static int deptId= 1;
	private final static int id = 1;
    /**
     * 插入收入结算表
     * @param params 全局值
     * @param currParam 当前循环值
     * @return
     * @throws Exception
     */
    @Transactional(rollbackFor = Exception.class)
    public static Integer insertSW_ST_TVM_INCOME_SETTLE(Map<String, Object> params, JSONObject currParam) throws Exception {
        SwStTvmIncomeSettleMapper swStTvmIncomeSettleMapper=SpringContextHolder.getBean(SwStTvmIncomeSettleMapper.class);
        Integer res=0;
        List<Map<String, Object>> maps = queryWillAdd_SW_ST_TVM_INCOME_SETTLE(params,currParam);
        //是否是自动提交
		Boolean isTaskAutoSubmit = Boolean.valueOf(String.valueOf(params.get("isTaskAutoSubmit")));
        for(int i=0;i<maps.size();i++){
            Map<String, Object> map= maps.get(i);
            SwStTvmIncomeSettle swStTvmIncomeSettle=new SwStTvmIncomeSettle();
            swStTvmIncomeSettle.setLineCode((String) map.get("line_code"));
            swStTvmIncomeSettle.setStationCode((String)map.get("station_code"));
            swStTvmIncomeSettle.setTvmCode((String)map.get("tvm_code"));
            Date recordetime = DateFormat.panDuanDate(map.get("record_time"));
            swStTvmIncomeSettle.setRecordTime(recordetime);
            swStTvmIncomeSettle.setDelFlag("0");

            QueryWrapper<SwStTvmIncomeSettle> queryWrapper1=new QueryWrapper<>();
            queryWrapper1.setEntity(swStTvmIncomeSettle);
            List<SwStTvmIncomeSettle> swStTvmIncomeSettles = swStTvmIncomeSettleMapper.selectList(queryWrapper1);
            //如果含有数据，则准备进行修改
            if(swStTvmIncomeSettles.size()>0){
                swStTvmIncomeSettle=swStTvmIncomeSettles.get(0);
            }
            
            Object startSettleDate = currParam.get("js_startSettleDate");
            Object endSettleDate = currParam.get("js_endSettleDate");
            Date settleStartTime = DateFormat.panDuanDate(startSettleDate);
            swStTvmIncomeSettle.setSettleStartTime(settleStartTime);
//          
            Date settleEndTime = DateFormat.panDuanDate(endSettleDate);
            swStTvmIncomeSettle.setSettleEndTime(settleEndTime);

            swStTvmIncomeSettle.setSettleTime(new Date());
            swStTvmIncomeSettle.setCashReceiptAmount((BigDecimal)map.get("cash_receipt_amount"));
			swStTvmIncomeSettle.setCheckAmount((BigDecimal)map.get("check_amount"));
            swStTvmIncomeSettle.setBillCashCashIncomeAmount((BigDecimal)map.get("bill_cash_cash_income_amount"));
            swStTvmIncomeSettle.setBillCashCashReceiptAmount((BigDecimal)map.get("bill_cash_cash_receipt_amount"));
            swStTvmIncomeSettle.setBillCashCashChangeAmount((BigDecimal)map.get("bill_cash_cash_change_amount"));

            swStTvmIncomeSettle.setItvmPaymentAmount((BigDecimal)map.get("itvm_payment_amount"));
            swStTvmIncomeSettle.setPassengerAttendantId(Integer.parseInt(String.valueOf(map.get("passenger_attendant_id"))));
            swStTvmIncomeSettle.setPassengerAttendantJobNo((String) map.get("passenger_attendant_job_no"));
            swStTvmIncomeSettle.setPassengerAttendantName((String) map.get("passenger_attendant_name"));

            swStTvmIncomeSettle.setAttendancePersonId(Integer.parseInt(String.valueOf(map.get("attendance_person_id"))));
            swStTvmIncomeSettle.setAttendancePersonJobNo((String) map.get("attendance_person_job_no"));
            swStTvmIncomeSettle.setAttendancePersonName((String) map.get("attendance_person_name"));

            Object settleDays = map.get("settle_days");
            swStTvmIncomeSettle.setSettleDays(Integer.parseInt(String.valueOf(settleDays)));
			if(!isTaskAutoSubmit){
			    swStTvmIncomeSettle.setDeptId(UserInfo.getDeptId());
                swStTvmIncomeSettle.setCreatorId(UserInfo.getId());
                swStTvmIncomeSettle.setCreatorName(UserInfo.getUserName());
			}else{
			    swStTvmIncomeSettle.setDeptId(deptId);
                swStTvmIncomeSettle.setCreatorId(id);
                swStTvmIncomeSettle.setCreatorName(userName);
			}
           
            swStTvmIncomeSettle.setCreateTime(new Date());
            swStTvmIncomeSettle.setUpdateTime(new Date());
            swStTvmIncomeSettle.setSettleStatus((String) map.get("settle_status"));
            swStTvmIncomeSettle.setIsAddReportError((String) map.get("is_add_report_error"));


            Integer updateReprotCounts = swStTvmIncomeSettle.getUpdateReprotCounts();
            if((Boolean) params.getOrDefault("isReport",false)){ //报告
                swStTvmIncomeSettle.setUpdateReprotCounts(updateReprotCounts==null?1:updateReprotCounts+1);
            }else{
                swStTvmIncomeSettle.setUpdateReprotCounts(updateReprotCounts==null?0:updateReprotCounts);
            }


            swStTvmIncomeSettle.setAdditionalRecordingStatus("0");


            if(swStTvmIncomeSettles.size()>0){
                res=swStTvmIncomeSettleMapper.updateById(swStTvmIncomeSettle);
            }else{
                res=swStTvmIncomeSettleMapper.insertswStTvmIncomeSettle(swStTvmIncomeSettle);
            }

        }
        return res;
    }

    /**
     * 先查询收入结算要添加的数据，准备收入结算数据
     * @param param
     * @return
     * @throws Exception
     */
    private static List<Map<String,Object>> queryWillAdd_SW_ST_TVM_INCOME_SETTLE(Map<String,Object> param,JSONObject currParam) throws Exception {
	        SysUserMapper sysUserMapper = SpringContextHolder.getBean(SysUserMapper.class);
	        Object ids = param.get("ids");
	        Object startSettleDate = currParam.get("js_startSettleDate");
	        Object endSettleDate = currParam.get("js_endSettleDate");
	
	        String startDate=DateFormat.ex_strDate_to_date(startSettleDate+"","yyyy-MM-dd");
	        String endDate=DateFormat.ex_strDate_to_date(endSettleDate+"","yyyy-MM-dd");
	        Object lineCode = param.get("lineCode");
	        Object stationCode = param.get("stationCode");
	        Object attendancePersonJobNo = param.get("attendancePersonJobNo");
	
	        QueryWrapper<SysUser> queryWrapper=new QueryWrapper<>();
	        queryWrapper.eq("user_code",attendancePersonJobNo);
	        List<SysUser> sysUsers = sysUserMapper.selectList(queryWrapper);
            //是否是自动提交
			Boolean isTaskAutoSubmit = Boolean.valueOf(String.valueOf(param.get("isTaskAutoSubmit")));
			if(!isTaskAutoSubmit){
			    if(sysUsers==null || sysUsers.size()==0){
	               throw new Exception("查询值班站长失败");
	            }
			}        
	        String sql = "select distinct scd.line_code line_code," +
	                "scd.station_code station_code," +
	                "scd.tvm_code tvm_code,\n" +
	                "'" + startSettleDate + "' settle_start_time," +
	                "'" + endSettleDate + "' settle_end_time," +
	                "scd.revenue_time record_time," +
	                "now() settle_time, \n" +
	                "(ifnull(scd.check_amount,0)  - ifnull(vsc.SUPPLEMENT_AMOUNT,0) + ifnull(vsc2.CLEAR_OUT_AMOUNT,0)) cash_receipt_amount,\n" + //收入结算表--现金实点金额
			        "ifnull(scd.check_amount,0) check_amount,\n" +
	                "0.00 bill_cash_cash_income_amount,\n" +//TVM结账单主表的现金总数
	                "0.00 bill_cash_cash_receipt_amount,\n" +//TVM结账单接收总计（硬币+纸币）
	                "0.00 bill_cash_cash_change_amount,\n" +//TVM结账单纸币找零+硬币找零
	                "0.00 itvm_payment_amount,\n" +
	                "" + UserInfo.getId() + " passenger_attendant_id,\n" ;
				if(!isTaskAutoSubmit){
				       sql = sql + "'" + UserInfo.getUserCode() + "' passenger_attendant_job_no,\n" +
		                "'" + UserInfo.getName() + "' passenger_attendant_name,\n" +
		                "" + sysUsers.get(0).getUserId() + " attendance_person_id,\n" +
		                "'" + sysUsers.get(0).getUserCode() + "' attendance_person_job_no,\n" +
		                "'" + sysUsers.get(0).getNikename() + "' attendance_person_name,\n" +
		                "DATEDIFF('" + endDate + "','" + startDate + "') settle_days,\n" +
		                "" + UserInfo.getDeptId() + " dept_id,\n" +
		                "" + UserInfo.getId() + " creator_id,\n" +
		                "'" + UserInfo.getName() + "' creator_name,\n" ;
				}else{
					
				         sql = sql + "scd.check_person_one_jop_no passenger_attendant_job_no,\n" +
		                "scd.check_person_one_name passenger_attendant_name,\n" +
		                "" + id + " attendance_person_id,\n" +//清点人2的ID未存储到库表内
		                "scd.check_person_two_jop_no attendance_person_job_no,\n" +
		                "scd.check_person_two_name attendance_person_name,\n" +
		                "DATEDIFF('" + endDate + "','" + startDate + "') settle_days,\n" +
		                "" + deptId+ " dept_id,\n" +
		                "" + id + " creator_id,\n" +
		                "'"+ userName + "' creator_name,\n" ;
				}
                
                  sql = sql +  "now() create_time,\n" +
				                "now() update_time,\n" +
				                "'1' settle_status,\n" +
				                "'0' is_add_report_error,\n" +
				                "0 update_reprot_counts,\n" +
				                "'0' del_flag\n" +
				                "        from sw_st_tvm_check_detail scd\n" +
				                "        left join \n" +
				                "        (select LINE_CODE,STATION_CODE,TVM_CODE,sum(SUPPLEMENT_AMOUNT) SUPPLEMENT_AMOUNT\n" +
				                "        \tfrom view_sw_st_tvm_supplement_currency \n" +
				                "\t\t\t\t\twhere SUPPLEMENT_CURRENCY_TIME between '" + startSettleDate + "' and '" + endSettleDate + "'\n" +
				                "        \tgroup by line_code,station_code,tvm_code) vsc on scd.line_code=vsc.line_code and scd.station_code=vsc.station_code and scd.tvm_code = vsc.tvm_code\n" +
				                "        left join \n" +
				                "        (select LINE_CODE,STATION_CODE,TVM_CODE,sum(CLEAR_OUT_AMOUNT+CLEAR_OUT_PAPER_AMOUNT) CLEAR_OUT_AMOUNT\n" +
				                "        \tfrom view_sw_st_handwork_clean_record \n" +
				                "\t\t\t\t\twhere CLEAN_TIME between '" + startSettleDate + "' and '" + endSettleDate + "'\n" +
				                "        \tgroup by line_code,station_code,tvm_code) vsc2 on scd.line_code=vsc2.line_code and scd.station_code=vsc2.station_code and scd.tvm_code = vsc2.tvm_code\n" +
				                "        left join sw_st_longperiod_settle_log sls on scd.line_code=sls.line_code and scd.station_code=sls.station_code and scd.tvm_code=sls.device_no\n" +
				                "        left join sw_st_tvm_income_settle ssis on scd.line_code=ssis.line_code and scd.station_code=ssis.station_code and scd.tvm_code=ssis.tvm_code and ssis.del_flag = '0'\n" +
				                "        where scd.id in (" + ids + ")";
	        List<Map<String, Object>> maps = JdbcUtil.exeSqlQuery(sql);
	        LOGGER.info("查詢收入結算预处理SQL----"+sql);
	        return maps;
    }


    /**
     * 插入·收入审核主表
     * @param params
     * @return
     * @throws SQLException
     * @throws ParseException
     */
    @Transactional(rollbackFor = Exception.class)
    public static Integer insert_sw_au_tvm_amount_defference(Map<String, Object> params, JSONObject currParam) throws Exception {
        SwAuTvmAmountDefferenceMapper swAuTvmAmountDefferenceMapper = SpringContextHolder.getBean(SwAuTvmAmountDefferenceMapper.class);
        SysUserMapper sysUserMapper = SpringContextHolder.getBean(SysUserMapper.class);

        List<Map<String, Object>> maps = queryWillAdd_sw_au_tvm_amount_defference(params,currParam);
        //是否是自动提交
	    Boolean isTaskAutoSubmit = Boolean.valueOf(String.valueOf(params.get("isTaskAutoSubmit")));
        Integer res=0;
        for(Map<String, Object> param:maps){
            SwAuTvmAmountDefference swAuTvmAmountDefference=new SwAuTvmAmountDefference();

            swAuTvmAmountDefference.setLineCode((String) param.get("line_code"));
            swAuTvmAmountDefference.setStationCode((String) param.get("station_code"));
            swAuTvmAmountDefference.setTvmCode((String) param.get("tvm_code"));
            Date record_time = DateFormat.panDuanDate(param.get("record_time"));
            swAuTvmAmountDefference.setRecordTime(record_time);
            swAuTvmAmountDefference.setDelFlag("0");

            QueryWrapper<SwAuTvmAmountDefference> queryWrapper1=new QueryWrapper<>();
            queryWrapper1.setEntity(swAuTvmAmountDefference);
            List<SwAuTvmAmountDefference> swAuTvmAmountDefferences = swAuTvmAmountDefferenceMapper.selectList(queryWrapper1);
            if(swAuTvmAmountDefferences.size()>0){
                swAuTvmAmountDefference=swAuTvmAmountDefferences.get(0);
            }
            swAuTvmAmountDefference.setIncomeSettleId((Integer) param.get("income_settle_id"));

            Date settleStartTime =DateFormat.panDuanDate((String) param.get("income_settle_begin_time"));
            Date settleEndTime =DateFormat.panDuanDate((String) param.get("income_settle_end_time"));
            //减一天数据
            settleEndTime=DateFormat.date_add_or_sub_days(settleEndTime,-1);
            swAuTvmAmountDefference.setIncomeSettleBeginTime(settleStartTime);
            swAuTvmAmountDefference.setIncomeSettleEndTime(settleEndTime);

            swAuTvmAmountDefference.setBoxUninstallStatus((String) param.get("box_uninstall_status"));

            swAuTvmAmountDefference.setSystemMoney((BigDecimal) param.get("system_money"));
            swAuTvmAmountDefference.setTvmBillOrderMoney((BigDecimal) param.get("tvm_bill_order_money"));

            //TVM结账单据接收纸币金额
            swAuTvmAmountDefference.setTvmBillReceivePapaer((BigDecimal)param.get("tvm_bill_receive_papaer"));
            //TVM结账单据接收硬币金额
            swAuTvmAmountDefference.setTvmBillReceiveCoins((BigDecimal)param.get("tvm_bill_receive_coins"));
            //TVM结账单据纸币找零金额
            swAuTvmAmountDefference.setTvmBillNoteChange((BigDecimal)param.get("tvm_bill_note_change"));
            //TVM结账单据硬币找零金额
            swAuTvmAmountDefference.setTvmBillCoinChange((BigDecimal)param.get("tvm_bill_coin_change"));
            //TVM结账单据现金售票金额
            swAuTvmAmountDefference.setTvmBillCashSellTicket((BigDecimal)param.get("tvm_bill_cash_sell_ticket"));
            //TVM结账单据现金充值金额
            swAuTvmAmountDefference.setTvmBillCashRecharge((BigDecimal)param.get("tvm_bill_cash_recharge"));
            //TVM结账单据现金退款金额
            swAuTvmAmountDefference.setTvmBillCashReturn((BigDecimal)param.get("tvm_bill_cash_return"));

            swAuTvmAmountDefference.setTvmBillElecSellTicket((BigDecimal)param.get("tvm_bill_elec_sell_ticket"));
            swAuTvmAmountDefference.setTvmBillElecRecharge((BigDecimal)param.get("tvm_bill_elec_recharge"));
            swAuTvmAmountDefference.setTvmBillElecReturn((BigDecimal)param.get("tvm_bill_elec_return"));           


            swAuTvmAmountDefference.setSupplementMoney((BigDecimal)param.get("supplement_money"));
            swAuTvmAmountDefference.setTvmUnloadMoney(new BigDecimal((long)param.get("tvm_unload_money")));
            swAuTvmAmountDefference.setActualMoney((BigDecimal)param.get("actual_money"));
            swAuTvmAmountDefference.setHandOutMoney((BigDecimal)param.get("hand_out_money"));
            swAuTvmAmountDefference.setDiffMoney((BigDecimal)param.get("diff_money"));

            swAuTvmAmountDefference.setStatementDiffAmount((BigDecimal)param.get("statement_diff_amount"));


            swAuTvmAmountDefference.setDataSource(String.valueOf(param.get("data_source")));
            swAuTvmAmountDefference.setSysDataSource("2");//账面差异

            swAuTvmAmountDefference.setSettleBillCashId(0);
            swAuTvmAmountDefference.setSettleBillElecId(0);

            swAuTvmAmountDefference.setAuditStatus(String.valueOf(param.get("audit_status")));
            swAuTvmAmountDefference.setAssistInvestStatus(String.valueOf(param.get("assist_invest_status")));
            swAuTvmAmountDefference.setIsAddSettleorderAssist(String.valueOf(param.get("is_add_settleorder_assist")));
            swAuTvmAmountDefference.setIsAddPricediffAssist(String.valueOf(param.get("is_add_pricediff_assist")));
            swAuTvmAmountDefference.setIsAddScLevelAssist(String.valueOf(param.get("is_add_sc_level_assist")));
            swAuTvmAmountDefference.setIsAddLcLevelAssist(String.valueOf(param.get("is_add_lc_level_assist")));
            swAuTvmAmountDefference.setIsAddAccLevelAssist(String.valueOf(param.get("is_add_acc_level_assist")));
            swAuTvmAmountDefference.setIsAddSinglerAssist(String.valueOf(param.get("is_add_singler_assist")));

			swAuTvmAmountDefference.setCreatorId(Integer.parseInt(String.valueOf(param.get("creator_id"))));
			swAuTvmAmountDefference.setCreatorName(String.valueOf(param.get("creator_name")));
            swAuTvmAmountDefference.setCreateTime(new Date());
            swAuTvmAmountDefference.setPassengerJobNo(String.valueOf(param.get("passenger_attendant_job_no")));
            swAuTvmAmountDefference.setPassengerJobName(String.valueOf(param.get("passenger_attendant_name")));     
			

            if(swAuTvmAmountDefferences.size()>0){
                res=swAuTvmAmountDefferenceMapper.updateById(swAuTvmAmountDefference);
            }else{
                res=swAuTvmAmountDefferenceMapper.insert(swAuTvmAmountDefference);
            }
        }

        return res;
    }


    /**
     * 先查询要添加的票款差异审核主表数据
     * @param param
     * @return
     * @throws Exception
     */
    private static List<Map<String,Object>> queryWillAdd_sw_au_tvm_amount_defference(Map<String,Object> param, JSONObject currParam) throws Exception {
        Object ids = param.get("ids");

        Object startSettleDate = currParam.get("js_startSettleDate");
        Object endSettleDate = currParam.get("js_endSettleDate");
        String startDate=DateFormat.ex_strDate_to_date(startSettleDate+"","yyyy-MM-dd");
        String endDate=DateFormat.ex_strDate_to_date(endSettleDate+"","yyyy-MM-dd");
        Object lineCode = param.get("lineCode");
        Object stationCode = param.get("stationCode");
        
         String sql=" select \n" +
                "'#{startDate}' income_settle_begin_time,\n" +
                "'#{endDate}' income_settle_end_time,\n" +
                "  scd.ID income_settle_id,\n" +
                "  scd.LINE_CODE line_code,\n" +
                "  scd.STATION_CODE station_code,\n" +
                "  scd.TVM_CODE tvm_code,\n" +
                "  case b.UNLOAD_STATUS when '0' then '1' else '2' end as box_uninstall_status,\n" +
                "  ifnull(scd.BILL_CASH_CASH_INCOME_AMOUNT,0)*100 system_money,\n" +   //实际是0
                "  ifnull(scd.BILL_CASH_CASH_INCOME_AMOUNT,0)*100 tvm_bill_order_money,\n" +
                "  0 tvm_unload_money,\n" +
                "  0.00 tvm_bill_receive_papaer,\n" +
                "  0.00 tvm_bill_receive_coins,\n" +
                "  0.00 tvm_bill_note_change,\n" +
                "  0.00 tvm_bill_coin_change,\n" +
                "  0.00 tvm_bill_cash_sell_ticket,\n" +
                "  0.00 tvm_bill_cash_recharge,\n" +
                "  0.00 tvm_bill_cash_return,\n" +
                "  0.00 tvm_bill_elec_sell_ticket,\n" +
                "  0.00 tvm_bill_elec_recharge,\n" +
                "  0.00 tvm_bill_elec_return,\n" +
                "  ifnull(vsc.SUPPLEMENT_AMOUNT,0)*100 supplement_money,\n" +
                "  ifnull(scd.CASH_RECEIPT_AMOUNT,0)*100 actual_money,\n" +//钱箱清点总金额-补币+手工清出，不变
                "  ifnull(vsc2.CLEAR_OUT_AMOUNT,0)*100 hand_out_money,\n" +
                "  ifnull(scd.CASH_RECEIPT_AMOUNT,0)*100 - ifnull(scd.BILL_CASH_CASH_INCOME_AMOUNT,0)*100 as diff_money,\n" + //差异金额需要调整
                "  0.00 statement_diff_amount,\n" +//原来是TVM清点金额-结账单金额算差异，实际是显示结账单自身差异  现在默认为0
                "  scd.RECORD_TIME record_time,\n" +
                "  '2' data_source,\n" +  //原来是结账单据，现在需要默认改为SC
                "  0 settle_bill_cash_id,\n" +
                "  0 settle_bill_elec_id,\n" +
                "  '0' audit_status,\n" +
                "  '0' assist_invest_status,\n" +
                "  '0' is_add_settleorder_assist,\n" +
                "  '0' is_add_pricediff_assist,\n" +
                "  '0' is_add_sc_level_assist,\n" +
                "  '0' is_add_lc_level_assist,\n" +
                "  '0' is_add_acc_level_assist,\n" +
                "  '0' is_add_singler_assist,\n" +
			    "   scd.passenger_attendant_id,\n"+//定时任务新增需要
			    "   scd.passenger_attendant_job_no,\n"+
			    "   scd.passenger_attendant_name,\n"+
			    "   scd.attendance_person_id,\n"+
			    "   scd.attendance_person_job_no,\n"+
			    "   scd.attendance_person_name,\n"+
			    "   scd.creator_id,\n"+
			    "   scd.creator_name,\n"+               
                "  now() create_time\n" +
                "from SW_ST_TVM_INCOME_SETTLE scd\n" +
                "left join view_sw_st_tvm_check_detail b on scd.line_code=b.line_code and scd.station_code=b.station_code and scd.tvm_code = b.tvm_code and scd.RECORD_TIME = b.REVENUE_TIME and scd.del_flag = '0'\n" +
                "left join \n" +
                "        (select LINE_CODE,STATION_CODE,TVM_CODE,sum(SUPPLEMENT_AMOUNT) SUPPLEMENT_AMOUNT\n" +
                "        \tfrom view_sw_st_tvm_supplement_currency \n" +
                "\t\t\t\t\twhere SUPPLEMENT_CURRENCY_TIME between '#{startSettleDate}' and '#{endSettleDate}'\n" +
                "        \tgroup by line_code,station_code,tvm_code) vsc on scd.line_code=vsc.line_code and scd.station_code=vsc.station_code and scd.tvm_code = vsc.tvm_code and scd.del_flag = '0'\n" +
                "left join \n" +
                "        (select LINE_CODE,STATION_CODE,TVM_CODE,sum(CLEAR_OUT_AMOUNT+CLEAR_OUT_PAPER_AMOUNT) CLEAR_OUT_AMOUNT\n" +
                "        \tfrom view_sw_st_handwork_clean_record \n" +
                "\t\t\t\t\twhere CLEAN_TIME between '#{startSettleDate}' and '#{endSettleDate}'\n" +
                "        \tgroup by line_code,station_code,tvm_code) vsc2 on scd.line_code=vsc2.line_code and scd.station_code=vsc2.station_code and scd.tvm_code = vsc2.tvm_code and scd.del_flag = '0'\n" +
                "where b.id in ("+ids+") and scd.del_flag = '0'";
        sql=sql.replace("#{startDate}",startDate).replace("#{endDate}",endDate);
        sql=sql.replace("#{startSettleDate}",startSettleDate+"").replace("#{endSettleDate}",endSettleDate+"");
        sql=sql.replace("#{lineCode}",lineCode+"").replace("#{stationCode}",stationCode+"");
		LOGGER.info("查詢TVM收入审核主表预处理SQL----"+sql);
        List<Map<String, Object>> maps = JdbcUtil.exeSqlQuery(sql);
        return maps;
    }


    /**
     * 插入·收入审核分钱箱主表
     * @param params
     * @return
     * @throws SQLException
     * @throws ParseException
     */
    @Transactional(rollbackFor = Exception.class)
    public static Integer insert_sw_au_tvm_box_amount_difference(Map<String, Object> params, JSONObject currParam) throws SQLException, ParseException {
        SwAuTvmBoxAmountDifferenceMapper swAuTvmBoxAmountDifferenceMapper = SpringContextHolder.getBean(SwAuTvmBoxAmountDifferenceMapper.class);
       
        List<Map<String, Object>> maps = queryWillAdd_sw_au_tvm_box_amount_difference(params,currParam,"sw_st_tvm_check_detail_coin");
        maps.addAll(queryWillAdd_sw_au_tvm_box_amount_difference(params,currParam,"sw_st_tvm_check_detail_coin_change"));
        maps.addAll(queryWillAdd_sw_au_tvm_box_amount_difference(params,currParam,"sw_st_tvm_check_detail_paper"));
        maps.addAll(queryWillAdd_sw_au_tvm_box_amount_difference(params,currParam,"sw_st_tvm_check_detail_paper_change"));
		maps.addAll(queryWillAdd_sw_au_tvm_box_amount_difference(params,currParam,"sw_st_tvm_check_detail_waste"));
        Integer res=0;
        for(Map<String, Object> param:maps){
            SwAuTvmBoxAmountDifference swAuTvmBoxAmountDifference=new SwAuTvmBoxAmountDifference();

            swAuTvmBoxAmountDifference.setLineCode(String.valueOf(param.get("line_code")));
            swAuTvmBoxAmountDifference.setStationCode(String.valueOf(param.get("station_code")));
            swAuTvmBoxAmountDifference.setTvmCode(String.valueOf(param.get("tvm_code")));
            Date record_time = DateFormat.panDuanDate(param.get("record_time"));
            swAuTvmBoxAmountDifference.setRecordTime(record_time);
            swAuTvmBoxAmountDifference.setCashBoxType(String.valueOf(param.get("cash_box_type")));
            swAuTvmBoxAmountDifference.setDelFlag("0");
            QueryWrapper<SwAuTvmBoxAmountDifference> queryWrapper=new QueryWrapper<>();
            queryWrapper.setEntity(swAuTvmBoxAmountDifference);
            List<SwAuTvmBoxAmountDifference> swAuTvmBoxAmountDifferences = swAuTvmBoxAmountDifferenceMapper.selectList(queryWrapper);
            boolean isExist = false;
            if(swAuTvmBoxAmountDifferences.size()>0){
                swAuTvmBoxAmountDifference=swAuTvmBoxAmountDifferences.get(0);
                isExist = true;
            }

            Date settleStartTime =DateFormat.panDuanDate(param.get("income_settle_begin_time"));
            Date settleEndTime =DateFormat.panDuanDate(param.get("income_settle_end_time"));
            //减一天数据
            settleEndTime=DateFormat.date_add_or_sub_days(settleEndTime,-1);
            swAuTvmBoxAmountDifference.setIncomeSettleBeginTime(settleStartTime);
            swAuTvmBoxAmountDifference.setIncomeSettleEndTime(settleEndTime);
            swAuTvmBoxAmountDifference.setIncomeSettleId((Integer) param.get("income_settle_id"));

            swAuTvmBoxAmountDifference.setRelationid(Integer.parseInt(String.valueOf(param.get("relationid"))));
            swAuTvmBoxAmountDifference.setCashBoxRelationId(Integer.parseInt(String.valueOf(param.get("cash_box_relation_id"))));


            //swAuTvmBoxAmountDifference.setCashBoxCode(String.valueOf(param.get("cash_box_code")));
            swAuTvmBoxAmountDifference.setBoxUninstallStatus(String.valueOf(param.get("box_uninstall_status")));

            swAuTvmBoxAmountDifference.setSystemMoney((BigDecimal) param.get("system_money"));
             
            swAuTvmBoxAmountDifference.setBillOrderMoney((BigDecimal) param.get("bill_order_money"));

           //获取SC接口数据金额，此时接口数据不具备，不需要查询  2021-03-16
            /*
            Map<String, Object> piaoKuanCY_box_sc = TvmUtil.getPiaoKuanCY_box_sc(param);
            BigDecimal sc_unload_amount=(BigDecimal) piaoKuanCY_box_sc.get("amount");
            BigDecimal supplement_amount=(BigDecimal) piaoKuanCY_box_sc.get("supplement_amount");
            BigDecimal sc_hand_out_amount=new BigDecimal(0);
            swAuTvmBoxAmountDifference.setScSaleAmount(sc_unload_amount.subtract(supplement_amount).add(sc_hand_out_amount));
            swAuTvmBoxAmountDifference.setScUnloadAmount(sc_unload_amount);
            swAuTvmBoxAmountDifference.setScSupplemntAmount(supplement_amount);
            swAuTvmBoxAmountDifference.setScHandOutAmount(sc_hand_out_amount);
            */
            swAuTvmBoxAmountDifference.setUnloadDisplayMoney((BigDecimal) param.get("unload_display_money"));
            swAuTvmBoxAmountDifference.setActualMoney((BigDecimal) param.get("actual_money"));
            swAuTvmBoxAmountDifference.setSupplementMoney((BigDecimal) param.get("supplement_money"));
            swAuTvmBoxAmountDifference.setHandOutMoney((BigDecimal)param.get("hand_out_money"));

            swAuTvmBoxAmountDifference.setDiffMoney((BigDecimal) param.get("diff_money"));

            if(!isExist){
            	swAuTvmBoxAmountDifference.setDataSource(String.valueOf(param.get("data_source")));

                swAuTvmBoxAmountDifference.setAuditStatus(String.valueOf(param.get("audit_status")));
                swAuTvmBoxAmountDifference.setAssistInvestStatus(String.valueOf(param.get("assist_invest_status")));
                swAuTvmBoxAmountDifference.setIsAddAssist(String.valueOf(param.get("is_add_assist")));
                swAuTvmBoxAmountDifference.setIsAddLevelAssist(String.valueOf(param.get("is_add_level_assist")));
                swAuTvmBoxAmountDifference.setCreatorId(Integer.parseInt(String.valueOf(param.get("creator_id"))));
                swAuTvmBoxAmountDifference.setCreatorName(String.valueOf(param.get("creator_name")));
                swAuTvmBoxAmountDifference.setCreateTime(new Date());
            	
            }
            swAuTvmBoxAmountDifference.setPassengerJobNo(String.valueOf(param.get("passenger_attendant_job_no")));
            swAuTvmBoxAmountDifference.setPassengerJobName(String.valueOf(param.get("passenger_attendant_name")));
            //正常提交不涉及更改报表差错更改人
            //if(isExist){
            //	 swAuTvmBoxAmountDifference.setUpdateJobNo(String.valueOf(param.get("passenger_attendant_job_no")));
            //     swAuTvmBoxAmountDifference.setUpdateJobName(String.valueOf(param.get("passenger_attendant_name")));
            //     swAuTvmBoxAmountDifference.setUpdateTime(new Date());
            //}
		
           

            if(isExist){
                res=swAuTvmBoxAmountDifferenceMapper.updateById(swAuTvmBoxAmountDifference);
            }else{
                res=swAuTvmBoxAmountDifferenceMapper.insert(swAuTvmBoxAmountDifference);
            }
        }
        return res;
    }

    /**
     * 查询分钱箱的数据
     * @param param
     * @return
     * @throws SQLException
     * @throws ParseException
     */
    public static List<Map<String, Object>> queryWillAdd_sw_au_tvm_box_amount_difference(Map<String,Object> param,JSONObject currParam, String money_box_type) throws SQLException, ParseException {
        Object ids = param.get("ids");

        Object startSettleDate = currParam.get("js_startSettleDate");
        Object endSettleDate = currParam.get("js_endSettleDate");
        String startDate = DateFormat.ex_strDate_to_date(startSettleDate + "", "yyyy-MM-dd");
        String endDate = DateFormat.ex_strDate_to_date(endSettleDate + "", "yyyy-MM-dd");
        Object lineCode = param.get("lineCode");
        Object stationCode = param.get("stationCode");
        //是否是自动提交
		Boolean isTaskAutoSubmit = Boolean.valueOf(String.valueOf(param.get("isTaskAutoSubmit")));
		
		  String queryCashBoxType  = "";
        //硬币回收箱
		    if(money_box_type.equals("sw_st_tvm_check_detail_coin")){
			    queryCashBoxType="1";
	        }
            //硬币找零箱
		    else if(money_box_type.equals("sw_st_tvm_check_detail_coin_change")){
	        	queryCashBoxType="2";
	        }
            //纸币回收箱
		    else if(money_box_type.equals("sw_st_tvm_check_detail_paper")){
	        	queryCashBoxType="3";
	        }
            //纸币找零箱
		    else if(money_box_type.equals("sw_st_tvm_check_detail_paper_change")){
	        	queryCashBoxType="4";
	        }
            //废钞箱
		    else if(money_box_type.equals("sw_st_tvm_check_detail_waste")){
	        	queryCashBoxType="5";
	        }
		
        
        String sql= "select \n" +
                "0 relationid,\n" +
                "'#{startDate}' income_settle_begin_time,\n" +
                "'#{endDate}' income_settle_end_time,\n" +
                "scd.INCOME_SETTLE_ID income_settle_id,\n" +
                "scd.CASH_BOX_RELATION_ID cash_box_relation_id,\n" +
                "\tscd.LINE_CODE line_code,\n" +
                "  scd.STATION_CODE station_code,\n" +
                "  scd.TVM_CODE tvm_code,\n" +
                " "+queryCashBoxType+"  cash_box_type,\n"+
                //"scd.CASHBOX_CODE cash_box_code,\n" +
                "case scd.UNLOAD_CASE when 3 then 1 else 2 end as box_uninstall_status,\n";

                //硬币回收箱
				if(queryCashBoxType.equals("1")){
	                   sql+=" 0.00 system_money,\n" +
	                         "0.00  bill_order_money,\n"+
                             "ifnull(vsc2.CLEAR_OUT_AMOUNT,0)*100 hand_out_money,\n" +
	                         "( ifnull(scd.REAL_AMOUNT,0) + ifnull(vsc2.CLEAR_OUT_AMOUNT,0))*100 as diff_money,\n"; 
	                   //if(!lineCode.equals("1")){//如果是硬币回收 1，且线路编号不为1  ，取补币表中钱箱类型为2的补币金额,    2021-04-11调整为统一算到硬币回收箱内
	                	   sql+="ifnull(vsc.SUPPLEMENT_AMOUNT,0)*100 supplement_money,\n"; //硬币补币
	                	   
	                   //}else{
	                	 //  sql+="0.00 supplement_money,\n";
	                   //}
	             }else if(queryCashBoxType.equals("2")){
	                    sql+=" 0.00 system_money,\n" +
	                          " 0.00  bill_order_money,\n"+
                              "0.00 hand_out_money,\n" +
	                          "ifnull(scd.REAL_AMOUNT,0)*100 diff_money,\n"+
	                          //"ifnull(vsc.SUPPLEMENT_AMOUNT,0)*100 supplement_money,\n"; //硬币找零 ,所有都不算补币金额
						      "0.00 supplement_money,\n";
	             }else if(queryCashBoxType.equals("3")){
	                    sql+="0.00 system_money,\n" +
	                         "0.00  bill_order_money,\n"+
                             "ifnull(vsc2.CLEAR_OUT_PAPER_AMOUNT,0)*100 hand_out_money,\n" +
	                         "( ifnull(scd.REAL_AMOUNT,0) + ifnull(vsc2.CLEAR_OUT_PAPER_AMOUNT,0))*100 as diff_money,\n" +
	                         "0.00 supplement_money,\n";
	             }else if(queryCashBoxType.equals("4")){
	                    sql+=" 0.00 system_money,\n" +
	                           "0.00 bill_order_money,\n"+
                               "0.00 hand_out_money,\n" +
                               "ifnull(scd.REAL_AMOUNT,0)*100 diff_money,\n"+
                               "ifnull(vsc.SUPPLEMENT_AMOUNT,0)*100 supplement_money,\n"; //纸币找零箱补币
	             }else if(queryCashBoxType.equals("5")){
	                    sql+= "0.00 system_money,\n" +
	                          "0.00  bill_order_money,\n"+
                              "0.00 hand_out_money,\n" +
                              "ifnull(scd.REAL_AMOUNT,0)*100 diff_money,\n"+
                              "0.00 supplement_money,\n";
	             }
               sql+="0 sc_sale_amount,\n" +
	                "0 sc_unload_amount,\n" +
	                "0 sc_hand_out_amount,\n" +
	                "0 sc_supplemnt_amount,\n" +
	                "ifnull(scd.SYS_AMOUNT,0)*100 unload_display_money,\n" +
	                "ifnull(scd.REAL_AMOUNT,0)*100 actual_money,\n" +
	                //"ifnull(vsc.SUPPLEMENT_AMOUNT,0)*100 supplement_money,\n" +
                    " scd.RECORD_TIME record_time,\n" +
	                "2 data_source,\n" +  //改为SC ，原来是结账单据
	                "0 audit_status,\n" +
	                "0 assist_invest_status,\n" +
	                "'' diff_reason,\n" +
	                "0 is_add_assist,\n" +
	                "0 is_add_level_assist,\n" +
	                "   scd.passenger_attendant_id,\n"+//定时任务新增需要
				    "   scd.passenger_attendant_job_no,\n"+
				    "   scd.passenger_attendant_name,\n"+
				    "   scd.attendance_person_id,\n"+
				    "   scd.attendance_person_job_no,\n"+
				    "   scd.attendance_person_name,\n"+
				    "   scd.creator_id,\n"+
				    "   scd.creator_name,\n"+				
	                "now() create_time\n" +
	                "from "+
	                " (select \n" +
		                "b.id sstcdId,"+
		                "scd.ID INCOME_SETTLE_ID,\n" +
		                "a.ID CASH_BOX_RELATION_ID,\n" +
		                "\tscd.LINE_CODE,\n" +
		                "  scd.STATION_CODE,\n" +
		                "  scd.TVM_CODE,\n" +
		                //"\ta.CASHBOX_CODE,\n" +
		                "a.UNLOAD_STATUS*1 UNLOAD_STATUS,\n" +
		                "a.UNLOAD_CASE UNLOAD_CASE,\n" +	
		                "a.SYS_AMOUNT,\n" +
		                "a.REAL_AMOUNT,\n" +
						"   scd.passenger_attendant_id,\n"+//定时任务新增需要
					    "   scd.passenger_attendant_job_no,\n"+
					    "   scd.passenger_attendant_name,\n"+
					    "   scd.attendance_person_id,\n"+
					    "   scd.attendance_person_job_no,\n"+
					    "   scd.attendance_person_name,\n"+
					    "   scd.creator_id,\n"+
					    "   scd.creator_name,\n"+			
			            "   scd.RECORD_TIME\n" +
		                "from "+money_box_type+" a,sw_st_tvm_check_detail b,SW_ST_TVM_INCOME_SETTLE scd \n" +
		                "where (scd.settle_status <>2 or scd.SETTLE_STATUS is null)\n" +
		                "        \tand scd.line_code='#{lineCode}'\n" +
		                "        \tand scd.station_code='#{stationCode}'\n" +
		                "        \tand date_add(scd.RECORD_TIME,interval 12 hour) between '#{startSettleDate}' and '#{endSettleDate}'\n" +
		                "        \tand scd.DEL_FLAG = '0'\n" +
		                "        \tand scd.line_code=b.line_code and scd.station_code=b.station_code and scd.tvm_code = b.tvm_code\n" +
		                "\t\t\t\t\tand date_add(b.revenue_time,interval 12 hour) between '#{startSettleDate}' and '#{endSettleDate}'\n" +
		                "        \tand b.ID = a.RELATION_ID and a.del_flag = '0') scd\n" ;
               

				/*  2021-03-16  补币问题处理
				  如果是纸币找零 4  ，取补币表中钱箱类型为4的补币金额
				  如果是硬币找零 2  ，取补币表中钱箱类型为2的补币金额 
                  如果是硬币回收 1，且线路编号不为1  ，取补币表中钱箱类型为2的补币金额
				  and scd.CASHBOX_CODE = vsc.TVM_CASH_BOX_NUM 换成钱箱类型条件，group by TVM_CASH_BOX_NUM和select中 条件去掉

				  分钱箱审核里面硬币找零箱，除1号线外其余线路均无硬币找零箱
				 
				  2021-04-11  调整为所有的硬币补币都算到硬币回收箱内，纸币算到纸币回收箱内。
				*/

				if(queryCashBoxType.equals("1")){// && !lineCode.equals("1")){//硬币回收箱
					  sql+= "left join \n" +
						    "        (select LINE_CODE,STATION_CODE,TVM_CODE,TVM_CASH_BOX_TYPE, sum(SUPPLEMENT_AMOUNT) SUPPLEMENT_AMOUNT\n" +
							"        \tfrom view_sw_st_tvm_supplement_currency \n" +
							"\t\t\t\t\twhere SUPPLEMENT_CURRENCY_TIME between '#{startSettleDate}' and '#{endSettleDate}'  and TVM_CASH_BOX_TYPE='2'\n" +
							"        \tgroup by line_code,station_code,tvm_code,TVM_CASH_BOX_TYPE) vsc \n"+
							"     on scd.line_code=vsc.line_code and scd.station_code=vsc.station_code and scd.tvm_code = vsc.tvm_code  and vsc.TVM_CASH_BOX_TYPE='2' \n" ;
	                   
	             }else if(queryCashBoxType.equals("2")){
	            	  /*
					  sql+= "left join \n" +
							 "        (select LINE_CODE,STATION_CODE,TVM_CODE,TVM_CASH_BOX_TYPE,sum(SUPPLEMENT_AMOUNT) SUPPLEMENT_AMOUNT\n" +
							 "        \tfrom view_sw_st_tvm_supplement_currency \n" +
							"\t\t\t\t\twhere SUPPLEMENT_CURRENCY_TIME between '#{startSettleDate}' and '#{endSettleDate}' and  TVM_CASH_BOX_TYPE='2' \n" +
							"        \tgroup by line_code,station_code,tvm_code,TVM_CASH_BOX_TYPE) vsc \n"+
							"     on scd.line_code=vsc.line_code and scd.station_code=vsc.station_code and scd.tvm_code = vsc.tvm_code  and vsc.TVM_CASH_BOX_TYPE='2' \n" ;
					  */
	             }else if(queryCashBoxType.equals("3")){
	                    
	             }else if(queryCashBoxType.equals("4")){
	            	 sql+= "left join \n" +
							 "        (select LINE_CODE,STATION_CODE,TVM_CODE,TVM_CASH_BOX_TYPE,sum(SUPPLEMENT_AMOUNT) SUPPLEMENT_AMOUNT\n" +
							 "        \tfrom view_sw_st_tvm_supplement_currency \n" +
							"\t\t\t\t\twhere SUPPLEMENT_CURRENCY_TIME between '#{startSettleDate}' and '#{endSettleDate}' and  TVM_CASH_BOX_TYPE='4' \n" +
							"        \tgroup by line_code,station_code,tvm_code,TVM_CASH_BOX_TYPE) vsc \n"+
							"     on scd.line_code=vsc.line_code and scd.station_code=vsc.station_code and scd.tvm_code = vsc.tvm_code  and vsc.TVM_CASH_BOX_TYPE='4' \n" ;
	             }else if(queryCashBoxType.equals("5")){
	                  
	             }				
				/*
                "left join \n" +
                "        (select LINE_CODE,STATION_CODE,TVM_CODE,TVM_CASH_BOX_NUM,sum(SUPPLEMENT_AMOUNT) SUPPLEMENT_AMOUNT\n" +
                "        \tfrom view_sw_st_tvm_supplement_currency \n" +
                "\t\t\t\t\twhere SUPPLEMENT_CURRENCY_TIME between '#{startSettleDate}' and '#{endSettleDate}'\n" +
                "        \tgroup by line_code,station_code,tvm_code,TVM_CASH_BOX_NUM) vsc on scd.line_code=vsc.line_code and scd.station_code=vsc.station_code and scd.tvm_code = vsc.tvm_code and scd.CASHBOX_CODE = vsc.TVM_CASH_BOX_NUM \n" +
               	*/
				if(queryCashBoxType.equals("1")|| (queryCashBoxType.equals("3"))){//手工清出金额算到硬币回收箱和纸币回收箱内
					
					sql+= "left join \n" +
			                "        (select LINE_CODE,STATION_CODE,TVM_CODE,sum(CLEAR_OUT_AMOUNT) CLEAR_OUT_AMOUNT," +
			                "           sum(CLEAR_OUT_PAPER_AMOUNT) CLEAR_OUT_PAPER_AMOUNT\n" +
			                "        \tfrom view_sw_st_handwork_clean_record \n" +
			                "\t\t\t\t\twhere CLEAN_TIME between '#{startSettleDate}' and '#{endSettleDate}'\n" +
			                "        \tgroup by line_code,station_code,tvm_code) vsc2 on scd.line_code=vsc2.line_code and scd.station_code=vsc2.station_code and scd.tvm_code = vsc2.tvm_code\n" ;
					
				}     

				sql+= "\nwhere scd.sstcdId in ("+ids+")";
        sql=sql.replace("#{startDate}",startDate).replace("#{endDate}",endDate);
        sql=sql.replace("#{startSettleDate}",startSettleDate+"").replace("#{endSettleDate}",endSettleDate+"");
        sql=sql.replace("#{lineCode}",lineCode+"").replace("#{stationCode}",stationCode+"");
		LOGGER.info("查詢分钱箱审核主表预处理SQL----"+sql);
        List<Map<String, Object>> maps = JdbcUtil.exeSqlQuery(sql);
        return maps;
    }

    /**
     * 补提交添加收入结算表
     * @param tvmCheckDetail
     * @return
     * @throws Exception
     */
    @Transactional(rollbackFor = Exception.class)
    public static Integer insertTvmIncomeSettleRepair(SwStTvmCheckDetail tvmCheckDetail, JSONObject jsonObject) throws Exception {
        SwStTvmIncomeSettleMapper swStTvmIncomeSettleMapper=SpringContextHolder.getBean(SwStTvmIncomeSettleMapper.class);
        Integer res=0;
        List<Map<String, Object>> maps = queryWillAddTvmIncomeSettleRepair(tvmCheckDetail,jsonObject);
        for(int i=0;i<maps.size();i++){
            Map<String, Object> map= maps.get(i);
            SwStTvmIncomeSettle swStTvmIncomeSettle=new SwStTvmIncomeSettle();
            swStTvmIncomeSettle.setLineCode((String) map.get("line_code"));
            swStTvmIncomeSettle.setStationCode((String)map.get("station_code"));
            swStTvmIncomeSettle.setTvmCode((String)map.get("tvm_code"));
            Date recordetime = DateFormat.panDuanDate(map.get("record_time"));
            swStTvmIncomeSettle.setRecordTime(recordetime);
            swStTvmIncomeSettle.setDelFlag("0");
            QueryWrapper<SwStTvmIncomeSettle> queryWrapper1=new QueryWrapper<>();
            queryWrapper1.setEntity(swStTvmIncomeSettle);
            List<SwStTvmIncomeSettle> swStTvmIncomeSettles = swStTvmIncomeSettleMapper.selectList(queryWrapper1);
            //如果含有数据，则准备进行修改
            if(swStTvmIncomeSettles.size()>0){
                swStTvmIncomeSettle=swStTvmIncomeSettles.get(0);
            }
            Object startSettleDate = jsonObject.get("startTime");
            Object endSettleDate = jsonObject.get("endTime");
            Date settleStartTime = DateFormat.panDuanDate(startSettleDate);
            swStTvmIncomeSettle.setSettleStartTime(settleStartTime);
            Date settleEndTime = DateFormat.panDuanDate(endSettleDate);
            swStTvmIncomeSettle.setSettleEndTime(settleEndTime);
            swStTvmIncomeSettle.setSettleTime(new Date());
            swStTvmIncomeSettle.setCashReceiptAmount((BigDecimal)map.get("cash_receipt_amount"));
            swStTvmIncomeSettle.setCheckAmount((BigDecimal)map.get("check_amount"));
            swStTvmIncomeSettle.setBillCashCashIncomeAmount((BigDecimal)map.get("bill_cash_cash_income_amount"));
            swStTvmIncomeSettle.setBillCashCashReceiptAmount((BigDecimal)map.get("bill_cash_cash_receipt_amount"));
            swStTvmIncomeSettle.setBillCashCashChangeAmount((BigDecimal)map.get("bill_cash_cash_change_amount"));
            swStTvmIncomeSettle.setItvmPaymentAmount((BigDecimal)map.get("itvm_payment_amount"));
            swStTvmIncomeSettle.setPassengerAttendantId(Integer.parseInt(String.valueOf(map.get("passenger_attendant_id"))));
            swStTvmIncomeSettle.setPassengerAttendantJobNo((String) map.get("passenger_attendant_job_no"));
            swStTvmIncomeSettle.setPassengerAttendantName((String) map.get("passenger_attendant_name"));
            swStTvmIncomeSettle.setAttendancePersonId(Integer.parseInt(String.valueOf(map.get("attendance_person_id"))));
            swStTvmIncomeSettle.setAttendancePersonJobNo((String) map.get("attendance_person_job_no"));
            swStTvmIncomeSettle.setAttendancePersonName((String) map.get("attendance_person_name"));
            Object settleDays = map.get("settle_days");
            swStTvmIncomeSettle.setSettleDays(Integer.parseInt(String.valueOf(settleDays)));
            swStTvmIncomeSettle.setDeptId(UserInfo.getDeptId());
            swStTvmIncomeSettle.setCreatorId(UserInfo.getId());
            swStTvmIncomeSettle.setCreatorName(UserInfo.getUserName());
            swStTvmIncomeSettle.setCreateTime(new Date());
            swStTvmIncomeSettle.setUpdateTime(new Date());
            swStTvmIncomeSettle.setSettleStatus((String) map.get("settle_status"));
            swStTvmIncomeSettle.setIsAddReportError((String) map.get("is_add_report_error"));
            Integer updateReprotCounts = swStTvmIncomeSettle.getUpdateReprotCounts();
             swStTvmIncomeSettle.setUpdateReprotCounts(updateReprotCounts==null?0:updateReprotCounts);
            swStTvmIncomeSettle.setDelFlag("0");
            swStTvmIncomeSettle.setAdditionalRecordingStatus("1");
            if(swStTvmIncomeSettles.size()>0){
                res=swStTvmIncomeSettleMapper.updateById(swStTvmIncomeSettle);
            }else{
                res=swStTvmIncomeSettleMapper.insertswStTvmIncomeSettle(swStTvmIncomeSettle);
            }
        }
        return res;
    }

    /**
     * 先查询收入结算要添加的数据，准备收入结算数据
     * @param tvmCheckDetail
     * @param jsonObject
     * @return
     * @throws Exception
     */
    private static List<Map<String,Object>> queryWillAddTvmIncomeSettleRepair(SwStTvmCheckDetail tvmCheckDetail,JSONObject jsonObject) throws Exception {
        SysUserMapper sysUserMapper = SpringContextHolder.getBean(SysUserMapper.class);
        Integer ids = tvmCheckDetail.getId();
        Object startSettleDate = jsonObject.get("startTime");
        Object endSettleDate = jsonObject.get("endTime");
        String startDate=DateFormat.ex_strDate_to_date(startSettleDate+"","yyyy-MM-dd");
        String endDate=DateFormat.ex_strDate_to_date(endSettleDate+"","yyyy-MM-dd");
        String lineCode = tvmCheckDetail.getLineCode();
        String stationCode = tvmCheckDetail.getStationCode();
        String attendancePersonJobNo = jsonObject.getString("attendancePersonJobNo");
        QueryWrapper<SysUser> queryWrapper=new QueryWrapper<>();
        queryWrapper.eq("user_code",attendancePersonJobNo);
        List<SysUser> sysUsers = sysUserMapper.selectList(queryWrapper);
        String sql = "select distinct scd.line_code line_code," +
                "scd.station_code station_code," +
                "scd.tvm_code tvm_code,\n" +
                "'" + startSettleDate + "' settle_start_time," +
                "'" + endSettleDate + "' settle_end_time," +
                "scd.revenue_time record_time," +
                "now() settle_time, \n" +
                "(ifnull(scd.check_amount,0)  - ifnull(vsc.SUPPLEMENT_AMOUNT,0) + ifnull(vsc2.CLEAR_OUT_AMOUNT,0)) cash_receipt_amount,\n" + //收入结算表--现金实点金额
                "ifnull(scd.check_amount,0) check_amount,\n" +
                "0.00 bill_cash_cash_income_amount,\n" +//TVM结账单主表的现金总数
                "0.00 bill_cash_cash_receipt_amount,\n" +//TVM结账单接收总计（硬币+纸币）
                "0.00 bill_cash_cash_change_amount,\n" +//TVM结账单纸币找零+硬币找零
                "0.00 itvm_payment_amount,\n" +
                "" + UserInfo.getId() + " passenger_attendant_id,\n" ;
        sql = sql + "'" + UserInfo.getUserCode() + "' passenger_attendant_job_no,\n" +
                "'" + UserInfo.getName() + "' passenger_attendant_name,\n" +
                "" + sysUsers.get(0).getUserId() + " attendance_person_id,\n" +
                "'" + sysUsers.get(0).getUserCode() + "' attendance_person_job_no,\n" +
                "'" + sysUsers.get(0).getNikename() + "' attendance_person_name,\n" +
                "DATEDIFF('" + endDate + "','" + startDate + "') settle_days,\n" +
                "" + UserInfo.getDeptId() + " dept_id,\n" +
                "" + UserInfo.getId() + " creator_id,\n" +
                "'" + UserInfo.getName() + "' creator_name,\n" ;
        sql = sql +  "now() create_time,\n" +
                "now() update_time,\n" +
                "'1' settle_status,\n" +
                "'0' is_add_report_error,\n" +
                "0 update_reprot_counts,\n" +
                "'0' del_flag\n" +
                "        from sw_st_tvm_check_detail scd\n" +
                "        left join \n" +
                "        (select LINE_CODE,STATION_CODE,TVM_CODE,sum(SUPPLEMENT_AMOUNT) SUPPLEMENT_AMOUNT\n" +
                "        \tfrom view_sw_st_tvm_supplement_currency \n" +
                "\t\t\t\t\twhere SUPPLEMENT_CURRENCY_TIME between '" + startSettleDate + "' and '" + endSettleDate + "'\n" +
                "        \tgroup by line_code,station_code,tvm_code) vsc on scd.line_code=vsc.line_code and scd.station_code=vsc.station_code and scd.tvm_code = vsc.tvm_code\n" +
                "        left join \n" +
                "        (select LINE_CODE,STATION_CODE,TVM_CODE,sum(CLEAR_OUT_AMOUNT+CLEAR_OUT_PAPER_AMOUNT) CLEAR_OUT_AMOUNT\n" +
                "        \tfrom view_sw_st_handwork_clean_record \n" +
                "\t\t\t\t\twhere CLEAN_TIME between '" + startSettleDate + "' and '" + endSettleDate + "'\n" +
                "        \tgroup by line_code,station_code,tvm_code) vsc2 on scd.line_code=vsc2.line_code and scd.station_code=vsc2.station_code and scd.tvm_code = vsc2.tvm_code\n" +
                "        left join sw_st_longperiod_settle_log sls on scd.line_code=sls.line_code and scd.station_code=sls.station_code and scd.tvm_code=sls.device_no\n" +
                "        left join sw_st_tvm_income_settle ssis on scd.line_code=ssis.line_code and scd.station_code=ssis.station_code and scd.tvm_code=ssis.tvm_code and ssis.del_flag='0'\n" +
                "        where scd.id in (" + ids + ")";
        List<Map<String, Object>> maps = JdbcUtil.exeSqlQuery(sql);
        LOGGER.info("查询收入结算预处理SQL----"+sql);
        return maps;
    }


    /**
     * 插入·收入审核主表
     * @param tvmCheckDetail
     * @return
     * @throws SQLException
     * @throws ParseException
     */
    @Transactional(rollbackFor = Exception.class)
    public static Integer insertAuTvmAmountDefference(SwStTvmCheckDetail tvmCheckDetail, JSONObject currParam) throws Exception {
        SwAuTvmAmountDefferenceMapper swAuTvmAmountDefferenceMapper = SpringContextHolder.getBean(SwAuTvmAmountDefferenceMapper.class);
        SysUserMapper sysUserMapper = SpringContextHolder.getBean(SysUserMapper.class);
        List<Map<String, Object>> maps = queryWillAddAuTvmAmountDefference(tvmCheckDetail,currParam);
        SwAuTvmAmountDefference sysMaps = syncCashBoxSystemAmounts(tvmCheckDetail,currParam);
        PigxUser user = SecurityUtils.getUser();
        Integer res=0;
        for(Map<String, Object> param:maps){
            SwAuTvmAmountDefference swAuTvmAmountDefference=new SwAuTvmAmountDefference();
            swAuTvmAmountDefference.setLineCode((String) param.get("line_code"));
            swAuTvmAmountDefference.setStationCode((String) param.get("station_code"));
            swAuTvmAmountDefference.setTvmCode((String) param.get("tvm_code"));
            Date record_time = DateFormat.panDuanDate(param.get("record_time"));
            swAuTvmAmountDefference.setRecordTime(record_time);
            swAuTvmAmountDefference.setDelFlag("0");
            QueryWrapper<SwAuTvmAmountDefference> queryWrapper1=new QueryWrapper<>();
            queryWrapper1.setEntity(swAuTvmAmountDefference);
            List<SwAuTvmAmountDefference> swAuTvmAmountDefferences = swAuTvmAmountDefferenceMapper.selectList(queryWrapper1);
            if(swAuTvmAmountDefferences.size()>0){
                swAuTvmAmountDefference=swAuTvmAmountDefferences.get(0);
            }
            swAuTvmAmountDefference.setIncomeSettleId((Integer) param.get("income_settle_id"));
            Date settleStartTime =DateFormat.panDuanDate((String) param.get("income_settle_begin_time"));
            Date settleEndTime =DateFormat.panDuanDate((String) param.get("income_settle_end_time"));
            settleEndTime=DateFormat.date_add_or_sub_days(settleEndTime,-1);//减一天数据
            swAuTvmAmountDefference.setIncomeSettleBeginTime(settleStartTime);
            swAuTvmAmountDefference.setIncomeSettleEndTime(settleEndTime);
            swAuTvmAmountDefference.setBoxUninstallStatus((String) param.get("box_uninstall_status"));
            swAuTvmAmountDefference.setSystemMoney((BigDecimal) param.get("system_money"));
            swAuTvmAmountDefference.setTvmBillOrderMoney((BigDecimal) param.get("tvm_bill_order_money"));
            //TVM结账单据接收纸币金额
            swAuTvmAmountDefference.setTvmBillReceivePapaer((BigDecimal)param.get("tvm_bill_receive_papaer"));
            //TVM结账单据接收硬币金额
            swAuTvmAmountDefference.setTvmBillReceiveCoins((BigDecimal)param.get("tvm_bill_receive_coins"));
            //TVM结账单据纸币找零金额
            swAuTvmAmountDefference.setTvmBillNoteChange((BigDecimal)param.get("tvm_bill_note_change"));
            //TVM结账单据硬币找零金额
            swAuTvmAmountDefference.setTvmBillCoinChange((BigDecimal)param.get("tvm_bill_coin_change"));
            //TVM结账单据现金售票金额
            swAuTvmAmountDefference.setTvmBillCashSellTicket((BigDecimal)param.get("tvm_bill_cash_sell_ticket"));
            //TVM结账单据现金充值金额
            swAuTvmAmountDefference.setTvmBillCashRecharge((BigDecimal)param.get("tvm_bill_cash_recharge"));
            //TVM结账单据现金退款金额
            swAuTvmAmountDefference.setTvmBillCashReturn((BigDecimal)param.get("tvm_bill_cash_return"));
            swAuTvmAmountDefference.setTvmBillElecSellTicket((BigDecimal)param.get("tvm_bill_elec_sell_ticket"));
            swAuTvmAmountDefference.setTvmBillElecRecharge((BigDecimal)param.get("tvm_bill_elec_recharge"));
            swAuTvmAmountDefference.setTvmBillElecReturn((BigDecimal)param.get("tvm_bill_elec_return"));
            swAuTvmAmountDefference.setSupplementMoney((BigDecimal)param.get("supplement_money"));
            swAuTvmAmountDefference.setTvmUnloadMoney(new BigDecimal((long)param.get("tvm_unload_money")));
            swAuTvmAmountDefference.setActualMoney((BigDecimal)param.get("actual_money"));
            swAuTvmAmountDefference.setHandOutMoney((BigDecimal)param.get("hand_out_money"));
            swAuTvmAmountDefference.setDiffMoney((BigDecimal)param.get("diff_money"));
            swAuTvmAmountDefference.setStatementDiffAmount((BigDecimal)param.get("statement_diff_amount"));
            swAuTvmAmountDefference.setDataSource(String.valueOf(param.get("data_source")));
            //账面差异
            swAuTvmAmountDefference.setSysDataSource("2");
            swAuTvmAmountDefference.setSettleBillCashId(0);
            swAuTvmAmountDefference.setSettleBillElecId(0);
            swAuTvmAmountDefference.setAuditStatus(String.valueOf(param.get("audit_status")));
            swAuTvmAmountDefference.setAssistInvestStatus(String.valueOf(param.get("assist_invest_status")));
            swAuTvmAmountDefference.setIsAddSettleorderAssist(String.valueOf(param.get("is_add_settleorder_assist")));
            swAuTvmAmountDefference.setIsAddPricediffAssist(String.valueOf(param.get("is_add_pricediff_assist")));
            swAuTvmAmountDefference.setIsAddScLevelAssist(String.valueOf(param.get("is_add_sc_level_assist")));
            swAuTvmAmountDefference.setIsAddLcLevelAssist(String.valueOf(param.get("is_add_lc_level_assist")));
            swAuTvmAmountDefference.setIsAddAccLevelAssist(String.valueOf(param.get("is_add_acc_level_assist")));
            swAuTvmAmountDefference.setIsAddSinglerAssist(String.valueOf(param.get("is_add_singler_assist")));
            swAuTvmAmountDefference.setCreatorId(Integer.parseInt(String.valueOf(param.get("creator_id"))));
            swAuTvmAmountDefference.setCreatorName(String.valueOf(param.get("creator_name")));
            swAuTvmAmountDefference.setCreateTime(new Date());
            swAuTvmAmountDefference.setPassengerJobNo(tvmCheckDetail.getCheckPersonTwoJopNo());
            swAuTvmAmountDefference.setPassengerJobName(tvmCheckDetail.getCheckPersonTwoName());
            /*swAuTvmAmountDefference.setPassengerJobNo(String.valueOf(param.get("passenger_attendant_job_no")));
            swAuTvmAmountDefference.setPassengerJobName(String.valueOf(param.get("passenger_attendant_name")));*/
            swAuTvmAmountDefference.setAdditionalRecordingStatus("1");
            //系统金额
            //SC
            swAuTvmAmountDefference.setScCashSellTicket(sysMaps.getScCashSellTicket());
            swAuTvmAmountDefference.setScCashRecharge(sysMaps.getScCashRecharge());
            swAuTvmAmountDefference.setScCashReturn(sysMaps.getScCashReturn());
            BigDecimal add = sysMaps.getScCashSellTicket().add(sysMaps.getScCashRecharge()).add(sysMaps.getScCashReturn());
            BigDecimal actualMoney = (BigDecimal)param.get("actual_money");
            BigDecimal subtract = actualMoney.subtract(sysMaps.getScCashSellTicket()).subtract(sysMaps.getScCashRecharge()).subtract(sysMaps.getScCashReturn());
            swAuTvmAmountDefference.setSystemMoney(add);
            swAuTvmAmountDefference.setDiffMoney(subtract);
            //LC
            swAuTvmAmountDefference.setLcCashSellTicket(sysMaps.getLcCashSellTicket());
            swAuTvmAmountDefference.setLcCashRecharge(sysMaps.getLcCashRecharge());
            //ACC
            swAuTvmAmountDefference.setAccCashSellTicket(sysMaps.getAccCashSellTicket());
            swAuTvmAmountDefference.setAccCashRecharge(sysMaps.getAccCashRecharge());
            swAuTvmAmountDefference.setAccCashReturn(sysMaps.getAccCashReturn());
            swAuTvmAmountDefference.setRepairSubmitJobNo(user.getUserCode());
            swAuTvmAmountDefference.setRepairSubmitJobName(user.getNikeName());
            swAuTvmAmountDefference.setRepairSubmitTime(new Date());
            if(swAuTvmAmountDefferences.size()>0){
                res=swAuTvmAmountDefferenceMapper.updateById(swAuTvmAmountDefference);
            }else{
                res=swAuTvmAmountDefferenceMapper.insert(swAuTvmAmountDefference);
            }
        }
        return res;
    }
    public static SwAuTvmAmountDefference syncCashBoxSystemAmounts(SwStTvmCheckDetail tvmCheckDetail, JSONObject currParam)throws  Exception {
        String lineCode = tvmCheckDetail.getLineCode();
        String stationCode = tvmCheckDetail.getStationCode();
        String tvmCode =tvmCheckDetail.getTvmCode();
        String startSettleDate = currParam.getString("startTime");
        String endSettleDate = currParam.getString("endTime");
        Date endTimeD=DateFormat.stringToDate(endSettleDate);
        //利用Calendar 实现 Date日期-1天
        Calendar c = Calendar.getInstance();
        c.setTime(endTimeD);
        c.add(Calendar.DAY_OF_MONTH, -1);
        endTimeD = c.getTime();
        String startDate=DateFormat.ex_strDate_to_date(startSettleDate+"","yyyy-MM-dd");
        String endDate = DateFormat.date_ex_str(endTimeD);
        Object startTime = DateFormat.ex_strDate_to_date(startDate,"yyyyMMdd");
        Object endTime = DateFormat.ex_strDate_to_date(endDate,"yyyyMMdd");
        List<Map<String, Object>> respSC = JdbcUtil.exeSqlQuery("select ifnull(sum(sale_txnamtall),0) sale_txnamtall, ifnull(sum(recharge_txnamtall),0) recharge_txnamtall,ifnull(sum(refund_txnamtall),0) refund_txnamtall from sc_fare_statistics " +
                "where equipmenttype='1'and station_equipment_number='" + tvmCode + "'" +
                " and (paymethod='0' or paymethod='1' or paymethod='10' OR paymethod is NULL or paymethod like '%3130%') and lineid='" + lineCode +"'" +
                " and station_number='" + stationCode +"'" +
                " and bookdate >='" +startTime+"' and bookdate <='"+endTime+"'", "sale_txnamtall,recharge_txnamtall,refund_txnamtall");
        List<Map<String, Object>> respLC = JdbcUtil.exeSqlQuery("select ifnull(sum(sale_txnamtall),0) sale_txnamtall, ifnull(sum(recharge_txnamtall),0) recharge_txnamtall,ifnull(sum(refund_txnamtall),0) refund_txnamtall from lc_fare_statistics " +
                "where equipmenttype='1'and station_equipment_number='" + tvmCode + "'" +
                " and (paymethod='0' or paymethod='1' or paymethod='10' OR paymethod is NULL or paymethod like '%3130%') and lineid='" + lineCode +"'" +
                " and station_number='" + stationCode +"'" +
                " and bookdate >='" +startTime+"' and bookdate <='"+endTime+"'", "sale_txnamtall,recharge_txnamtall,refund_txnamtall");
        List<Map<String, Object>> respACC = JdbcUtil.exeSqlQuery("select ifnull(sum(sale_txnamtall),0) sale_txnamtall, ifnull(sum(recharge_txnamtall),0) recharge_txnamtall,ifnull(sum(refund_txnamtall),0) refund_txnamtall from acc_fare_statistics " +
                "where equipmenttype='1'and station_equipment_number='" + tvmCode + "'" +
                " and (paymethod='0' or paymethod='1' or paymethod='10' OR paymethod is NULL or paymethod like '%3130%') and lineid='" + lineCode +"'" +
                " and station_number='" + stationCode +"'" +
                " and bookdate >='" +startTime+"' and bookdate <='"+endTime+"'", "sale_txnamtall,recharge_txnamtall,refund_txnamtall");
        SwAuTvmAmountDefference swAuTvmAmountDefference = new SwAuTvmAmountDefference();
        if(null != respSC && 0 != respSC.size()){
            Map<String, Object> map = respSC.get(0);
            BigDecimal sale_txnamtall = BigDecimal.valueOf(Long.valueOf(String.valueOf(map.get("sale_txnamtall"))));
            BigDecimal recharge_txnamtall = BigDecimal.valueOf(Long.valueOf(String.valueOf(map.get("recharge_txnamtall"))));
            BigDecimal refund_txnamtall = BigDecimal.valueOf(Long.valueOf(String.valueOf(map.get("refund_txnamtall"))));
            swAuTvmAmountDefference.setScCashSellTicket(sale_txnamtall);
            swAuTvmAmountDefference.setScCashRecharge(recharge_txnamtall);
            swAuTvmAmountDefference.setScCashReturn(refund_txnamtall);
        }
        if(null != respLC && 0 != respLC.size()){
            Map<String, Object> map = respLC.get(0);
            BigDecimal sale_txnamtall = BigDecimal.valueOf(Long.valueOf(String.valueOf(map.get("sale_txnamtall"))));
            BigDecimal recharge_txnamtall = BigDecimal.valueOf(Long.valueOf(String.valueOf(map.get("recharge_txnamtall"))));
            swAuTvmAmountDefference.setLcCashSellTicket(sale_txnamtall);
            swAuTvmAmountDefference.setLcCashRecharge(recharge_txnamtall);
        }
        if(null != respACC && 0 != respACC.size()){
            Map<String, Object> map = respACC.get(0);
            BigDecimal sale_txnamtall = BigDecimal.valueOf(Long.valueOf(String.valueOf(map.get("sale_txnamtall"))));
            BigDecimal recharge_txnamtall = BigDecimal.valueOf(Long.valueOf(String.valueOf(map.get("recharge_txnamtall"))));
            BigDecimal refund_txnamtall = BigDecimal.valueOf(Long.valueOf(String.valueOf(map.get("refund_txnamtall"))));
            swAuTvmAmountDefference.setAccCashSellTicket(sale_txnamtall);
            swAuTvmAmountDefference.setAccCashRecharge(recharge_txnamtall);
            swAuTvmAmountDefference.setAccCashReturn(refund_txnamtall);
        }
        return swAuTvmAmountDefference;
    }
    /**
     * 先查询要添加的票款差异审核主表数据
     * @param tvmCheckDetail
     * @return
     * @throws Exception
     */
    private static List<Map<String,Object>> queryWillAddAuTvmAmountDefference(SwStTvmCheckDetail tvmCheckDetail, JSONObject currParam) throws Exception {
        Integer ids = tvmCheckDetail.getId();
        Object startSettleDate = currParam.get("startTime");
        Object endSettleDate = currParam.get("endTime");
        String startDate=DateFormat.ex_strDate_to_date(startSettleDate+"","yyyy-MM-dd");
        String endDate=DateFormat.ex_strDate_to_date(endSettleDate+"","yyyy-MM-dd");
        Object lineCode = tvmCheckDetail.getLineCode();
        Object stationCode = tvmCheckDetail.getStationCode();
        String sql=" select \n" +
                "'#{startDate}' income_settle_begin_time,\n" +
                "'#{endDate}' income_settle_end_time,\n" +
                "  scd.ID income_settle_id,\n" +
                "  scd.LINE_CODE line_code,\n" +
                "  scd.STATION_CODE station_code,\n" +
                "  scd.TVM_CODE tvm_code,\n" +
                "  case b.UNLOAD_STATUS when '0' then '1' else '2' end as box_uninstall_status,\n" +
                "  ifnull(scd.BILL_CASH_CASH_INCOME_AMOUNT,0)*100 system_money,\n" +   //实际是0
                "  ifnull(scd.BILL_CASH_CASH_INCOME_AMOUNT,0)*100 tvm_bill_order_money,\n" +
                "  0 tvm_unload_money,\n" +
                "  0.00 tvm_bill_receive_papaer,\n" +
                "  0.00 tvm_bill_receive_coins,\n" +
                "  0.00 tvm_bill_note_change,\n" +
                "  0.00 tvm_bill_coin_change,\n" +
                "  0.00 tvm_bill_cash_sell_ticket,\n" +
                "  0.00 tvm_bill_cash_recharge,\n" +
                "  0.00 tvm_bill_cash_return,\n" +
                "  0.00 tvm_bill_elec_sell_ticket,\n" +
                "  0.00 tvm_bill_elec_recharge,\n" +
                "  0.00 tvm_bill_elec_return,\n" +
                "  ifnull(vsc.SUPPLEMENT_AMOUNT,0)*100 supplement_money,\n" +
                "  ifnull(scd.CASH_RECEIPT_AMOUNT,0)*100 actual_money,\n" +//钱箱清点总金额-补币+手工清出，不变
                "  ifnull(vsc2.CLEAR_OUT_AMOUNT,0)*100 hand_out_money,\n" +
                "  ifnull(scd.CASH_RECEIPT_AMOUNT,0)*100 - ifnull(scd.BILL_CASH_CASH_INCOME_AMOUNT,0)*100 as diff_money,\n" + //差异金额需要调整
                "  0.00 statement_diff_amount,\n" +//原来是TVM清点金额-结账单金额算差异，实际是显示结账单自身差异  现在默认为0
                "  scd.RECORD_TIME record_time,\n" +
                "  '2' data_source,\n" +  //原来是结账单据，现在需要默认改为SC
                "  0 settle_bill_cash_id,\n" +
                "  0 settle_bill_elec_id,\n" +
                "  '0' audit_status,\n" +
                "  '0' assist_invest_status,\n" +
                "  '0' is_add_settleorder_assist,\n" +
                "  '0' is_add_pricediff_assist,\n" +
                "  '0' is_add_sc_level_assist,\n" +
                "  '0' is_add_lc_level_assist,\n" +
                "  '0' is_add_acc_level_assist,\n" +
                "  '0' is_add_singler_assist,\n" +
                "   scd.passenger_attendant_id,\n"+//定时任务新增需要
                "   scd.passenger_attendant_job_no,\n"+
                "   scd.passenger_attendant_name,\n"+
                "   scd.attendance_person_id,\n"+
                "   scd.attendance_person_job_no,\n"+
                "   scd.attendance_person_name,\n"+
                "   scd.creator_id,\n"+
                "   scd.creator_name,\n"+
                "  now() create_time\n" +
                "from SW_ST_TVM_INCOME_SETTLE scd\n" +
                "left join view_sw_st_tvm_check_detail b on scd.line_code=b.line_code and scd.station_code=b.station_code and scd.tvm_code = b.tvm_code and scd.RECORD_TIME = b.REVENUE_TIME and scd.del_flag = '0'\n" +
                "left join \n" +
                "        (select LINE_CODE,STATION_CODE,TVM_CODE,sum(SUPPLEMENT_AMOUNT) SUPPLEMENT_AMOUNT\n" +
                "        \tfrom view_sw_st_tvm_supplement_currency \n" +
                "\t\t\t\t\twhere SUPPLEMENT_CURRENCY_TIME between '#{startSettleDate}' and '#{endSettleDate}'\n" +
                "        \tgroup by line_code,station_code,tvm_code) vsc on scd.line_code=vsc.line_code and scd.station_code=vsc.station_code and scd.tvm_code = vsc.tvm_code and scd.del_flag = '0'\n" +
                "left join \n" +
                "        (select LINE_CODE,STATION_CODE,TVM_CODE,sum(CLEAR_OUT_AMOUNT+CLEAR_OUT_PAPER_AMOUNT) CLEAR_OUT_AMOUNT\n" +
                "        \tfrom view_sw_st_handwork_clean_record \n" +
                "\t\t\t\t\twhere CLEAN_TIME between '#{startSettleDate}' and '#{endSettleDate}'\n" +
                "        \tgroup by line_code,station_code,tvm_code) vsc2 on scd.line_code=vsc2.line_code and scd.station_code=vsc2.station_code and scd.tvm_code = vsc2.tvm_code and scd.del_flag = '0'\n" +
                "where b.id in ("+ids+") and scd.DEL_FLAG ='0'";
        sql=sql.replace("#{startDate}",startDate).replace("#{endDate}",endDate);
        sql=sql.replace("#{startSettleDate}",startSettleDate+"").replace("#{endSettleDate}",endSettleDate+"");
        sql=sql.replace("#{lineCode}",lineCode+"").replace("#{stationCode}",stationCode+"");
        LOGGER.info("查詢TVM收入审核主表预处理SQL----"+sql);
        List<Map<String, Object>> maps = JdbcUtil.exeSqlQuery(sql);
        return maps;
    }


    /**
     * 插入·收入审核分钱箱主表
     * @param tvmCheckDetail
     * @return
     * @throws SQLException
     * @throws ParseException
     */
    @Transactional(rollbackFor = Exception.class)
    public static Integer insertAutvmBoxAmountDifference(SwStTvmCheckDetail tvmCheckDetail, JSONObject currParam) throws Exception {
        SwAuTvmBoxAmountDifferenceMapper swAuTvmBoxAmountDifferenceMapper = SpringContextHolder.getBean(SwAuTvmBoxAmountDifferenceMapper.class);
        List<Map<String, Object>> maps = queryWillAddAuTvmBoxAmountDifference(tvmCheckDetail,currParam,"sw_st_tvm_check_detail_coin");
        maps.addAll(queryWillAddAuTvmBoxAmountDifference(tvmCheckDetail,currParam,"sw_st_tvm_check_detail_coin_change"));
        maps.addAll(queryWillAddAuTvmBoxAmountDifference(tvmCheckDetail,currParam,"sw_st_tvm_check_detail_paper"));
        maps.addAll(queryWillAddAuTvmBoxAmountDifference(tvmCheckDetail,currParam,"sw_st_tvm_check_detail_paper_change"));
        maps.addAll(queryWillAddAuTvmBoxAmountDifference(tvmCheckDetail,currParam,"sw_st_tvm_check_detail_waste"));
        Integer res=0;
        for(Map<String, Object> param:maps){
            SwAuTvmBoxAmountDifference swAuTvmBoxAmountDifference=new SwAuTvmBoxAmountDifference();
            swAuTvmBoxAmountDifference.setLineCode(String.valueOf(param.get("line_code")));
            swAuTvmBoxAmountDifference.setStationCode(String.valueOf(param.get("station_code")));
            swAuTvmBoxAmountDifference.setTvmCode(String.valueOf(param.get("tvm_code")));
            Date record_time = DateFormat.panDuanDate(param.get("record_time"));
            swAuTvmBoxAmountDifference.setRecordTime(record_time);
            swAuTvmBoxAmountDifference.setCashBoxType(String.valueOf(param.get("cash_box_type")));
            swAuTvmBoxAmountDifference.setDelFlag("0");
            QueryWrapper<SwAuTvmBoxAmountDifference> queryWrapper=new QueryWrapper<>();
            queryWrapper.setEntity(swAuTvmBoxAmountDifference);
            List<SwAuTvmBoxAmountDifference> swAuTvmBoxAmountDifferences = swAuTvmBoxAmountDifferenceMapper.selectList(queryWrapper);
            boolean isExist = false;
            if(swAuTvmBoxAmountDifferences.size()>0){
                swAuTvmBoxAmountDifference=swAuTvmBoxAmountDifferences.get(0);
                isExist = true;
            }
            Date settleStartTime =DateFormat.panDuanDate(param.get("income_settle_begin_time"));
            Date settleEndTime =DateFormat.panDuanDate(param.get("income_settle_end_time"));
            //减一天数据
            settleEndTime=DateFormat.date_add_or_sub_days(settleEndTime,-1);
            swAuTvmBoxAmountDifference.setIncomeSettleBeginTime(settleStartTime);
            swAuTvmBoxAmountDifference.setIncomeSettleEndTime(settleEndTime);
            swAuTvmBoxAmountDifference.setIncomeSettleId((Integer) param.get("income_settle_id"));
            swAuTvmBoxAmountDifference.setRelationid(Integer.parseInt(String.valueOf(param.get("relationid"))));
            swAuTvmBoxAmountDifference.setCashBoxRelationId(Integer.parseInt(String.valueOf(param.get("cash_box_relation_id"))));
            //swAuTvmBoxAmountDifference.setCashBoxCode(String.valueOf(param.get("cash_box_code")));
            swAuTvmBoxAmountDifference.setBoxUninstallStatus(String.valueOf(param.get("box_uninstall_status")));
            swAuTvmBoxAmountDifference.setSystemMoney((BigDecimal) param.get("system_money"));
            swAuTvmBoxAmountDifference.setBillOrderMoney((BigDecimal) param.get("bill_order_money"));
            swAuTvmBoxAmountDifference.setUnloadDisplayMoney((BigDecimal) param.get("unload_display_money"));
            swAuTvmBoxAmountDifference.setActualMoney((BigDecimal) param.get("actual_money"));
            swAuTvmBoxAmountDifference.setSupplementMoney((BigDecimal) param.get("supplement_money"));
            swAuTvmBoxAmountDifference.setHandOutMoney((BigDecimal)param.get("hand_out_money"));
            swAuTvmBoxAmountDifference.setDiffMoney((BigDecimal) param.get("diff_money"));
            List<Map<String, Object>> clearOutAmount = getClearOutAmount(tvmCheckDetail,currParam);
            BigDecimal clearOutHeJiAmount=new BigDecimal(0);
            BigDecimal actual_money = (BigDecimal) param.get("actual_money");
            if("1".equals(String.valueOf(param.get("cash_box_type")))){
                BigDecimal clear_out_amount =(BigDecimal)clearOutAmount.get(0).get("clear_out_amount");
                clearOutHeJiAmount=actual_money.add(clear_out_amount);
                //String cashBoxCode = String.valueOf(param.get("cash_box_code"));
                String cashBoxType = String.valueOf(param.get("cash_box_type"));
                Map<String, Object> resp = syncCashBoxBatchSystemAmount(tvmCheckDetail,currParam,cashBoxType);
                BigDecimal amount = BigDecimal.valueOf(Long.valueOf(String.valueOf(resp.get("amount"))));
                BigDecimal supplemntAmount = BigDecimal.valueOf(Long.valueOf(String.valueOf(resp.get("supplement_amount"))));
                swAuTvmBoxAmountDifference.setSystemMoney(amount);
                swAuTvmBoxAmountDifference.setScUnloadAmount(amount);
                swAuTvmBoxAmountDifference.setDiffMoney(clearOutHeJiAmount.subtract(amount));
                swAuTvmBoxAmountDifference.setScSupplemntAmount(supplemntAmount);
            }else if("2".equals(String.valueOf(param.get("cash_box_type")))){
                clearOutHeJiAmount=actual_money;
                //String cashBoxCode = String.valueOf(param.get("cash_box_code"));
                String cashBoxType = String.valueOf(param.get("cash_box_type"));
                Map<String, Object> resp = syncCashBoxBatchSystemAmount(tvmCheckDetail,currParam,cashBoxType);
                BigDecimal amount = BigDecimal.valueOf(Long.valueOf(String.valueOf(resp.get("amount"))));
                BigDecimal supplemntAmount = BigDecimal.valueOf(Long.valueOf(String.valueOf(resp.get("supplement_amount"))));
                swAuTvmBoxAmountDifference.setSystemMoney(amount);
                swAuTvmBoxAmountDifference.setScUnloadAmount(amount);
                swAuTvmBoxAmountDifference.setDiffMoney(clearOutHeJiAmount.subtract(amount));
                swAuTvmBoxAmountDifference.setScSupplemntAmount(supplemntAmount);
            }else if("3".equals(String.valueOf(param.get("cash_box_type")))){
                BigDecimal clear_out_amount =(BigDecimal)clearOutAmount.get(0).get("clear_out_paper_amount");
                clearOutHeJiAmount=actual_money.add(clear_out_amount);
                //String cashBoxCode = String.valueOf(param.get("cash_box_code"));
                String cashBoxType = String.valueOf(param.get("cash_box_type"));
                Map<String, Object> resp = syncCashBoxBatchSystemAmount(tvmCheckDetail,currParam,cashBoxType);
                BigDecimal amount = BigDecimal.valueOf(Long.valueOf(String.valueOf(resp.get("amount"))));
                BigDecimal supplemntAmount = BigDecimal.valueOf(Long.valueOf(String.valueOf(resp.get("supplement_amount"))));
                swAuTvmBoxAmountDifference.setSystemMoney(amount);
                swAuTvmBoxAmountDifference.setScUnloadAmount(amount);
                swAuTvmBoxAmountDifference.setDiffMoney(clearOutHeJiAmount.subtract(amount));
                swAuTvmBoxAmountDifference.setScSupplemntAmount(supplemntAmount);
            }else if("4".equals(String.valueOf(param.get("cash_box_type")))){
                clearOutHeJiAmount=actual_money;
                //String cashBoxCode = String.valueOf(param.get("cash_box_code"));
                String cashBoxType = String.valueOf(param.get("cash_box_type"));
                Map<String, Object> resp = syncCashBoxBatchSystemAmount(tvmCheckDetail,currParam,cashBoxType);
                BigDecimal amount = BigDecimal.valueOf(Long.valueOf(String.valueOf(resp.get("amount"))));
                BigDecimal supplemntAmount = BigDecimal.valueOf(Long.valueOf(String.valueOf(resp.get("supplement_amount"))));
                swAuTvmBoxAmountDifference.setSystemMoney(amount);
                swAuTvmBoxAmountDifference.setScUnloadAmount(amount);
                swAuTvmBoxAmountDifference.setScUnloadAmount(amount);
                swAuTvmBoxAmountDifference.setDiffMoney(clearOutHeJiAmount.subtract(amount));
                swAuTvmBoxAmountDifference.setScSupplemntAmount(supplemntAmount);
            }else if("5".equals(String.valueOf(param.get("cash_box_type")))){
                clearOutHeJiAmount=actual_money;
                //String cashBoxCode = String.valueOf(param.get("cash_box_code"));
                String cashBoxType = String.valueOf(param.get("cash_box_type"));
                Map<String, Object> resp = syncCashBoxBatchSystemAmount(tvmCheckDetail,currParam,cashBoxType);
                BigDecimal amount = BigDecimal.valueOf(Long.valueOf(String.valueOf(resp.get("amount"))));
                BigDecimal supplemntAmount = BigDecimal.valueOf(Long.valueOf(String.valueOf(resp.get("supplement_amount"))));
                swAuTvmBoxAmountDifference.setSystemMoney(amount);
                swAuTvmBoxAmountDifference.setScUnloadAmount(amount);
                swAuTvmBoxAmountDifference.setDiffMoney(clearOutHeJiAmount.subtract(amount));
                swAuTvmBoxAmountDifference.setScSupplemntAmount(supplemntAmount);
            }
            if(!isExist){
                swAuTvmBoxAmountDifference.setDataSource(String.valueOf(param.get("data_source")));
                swAuTvmBoxAmountDifference.setAuditStatus(String.valueOf(param.get("audit_status")));
                swAuTvmBoxAmountDifference.setAssistInvestStatus(String.valueOf(param.get("assist_invest_status")));
                swAuTvmBoxAmountDifference.setIsAddAssist(String.valueOf(param.get("is_add_assist")));
                swAuTvmBoxAmountDifference.setIsAddLevelAssist(String.valueOf(param.get("is_add_level_assist")));
                swAuTvmBoxAmountDifference.setCreatorId(Integer.parseInt(String.valueOf(param.get("creator_id"))));
                swAuTvmBoxAmountDifference.setCreatorName(String.valueOf(param.get("creator_name")));
                swAuTvmBoxAmountDifference.setCreateTime(new Date());
            }
            swAuTvmBoxAmountDifference.setPassengerJobNo(tvmCheckDetail.getCheckPersonTwoJopNo());
            swAuTvmBoxAmountDifference.setPassengerJobName(tvmCheckDetail.getCheckPersonTwoName());
            /*swAuTvmBoxAmountDifference.setPassengerJobNo(String.valueOf(param.get("passenger_attendant_job_no")));
            swAuTvmBoxAmountDifference.setPassengerJobName(String.valueOf(param.get("passenger_attendant_name")));*/
            swAuTvmBoxAmountDifference.setAdditionalRecordingStatus("1");
            //补提交不涉及报表差错更改人
            //if(isExist){
            //    swAuTvmBoxAmountDifference.setUpdateJobNo(String.valueOf(param.get("passenger_attendant_job_no")));
            //    swAuTvmBoxAmountDifference.setUpdateJobName(String.valueOf(param.get("passenger_attendant_name")));
            //    swAuTvmBoxAmountDifference.setUpdateTime(new Date());
            //}
            if(isExist){
                res=swAuTvmBoxAmountDifferenceMapper.updateById(swAuTvmBoxAmountDifference);
            }else{
                res=swAuTvmBoxAmountDifferenceMapper.insert(swAuTvmBoxAmountDifference);
            }
        }
        return res;
    }



    /**
     * 获取手工清出金额
     * @param tvmCheckDetail
     * @return
     * @throws ParseException
     * @throws SQLException
     */
    public static List<Map<String, Object>> getClearOutAmount(SwStTvmCheckDetail tvmCheckDetail, JSONObject currParam) throws ParseException, SQLException {
        String lineCode = tvmCheckDetail.getLineCode();
        String stationCode = tvmCheckDetail.getStationCode();
        String tvmCode =tvmCheckDetail.getTvmCode();
        String startSettleDate = currParam.getString("startTime");
        String endSettleDate = currParam.getString("endTime");
        Date endTimeD=DateFormat.stringToDate(endSettleDate);
        //利用Calendar 实现 Date日期-1天
        Calendar c = Calendar.getInstance();
        c.setTime(endTimeD);
        c.add(Calendar.DAY_OF_MONTH, -1);
        endTimeD = c.getTime();
        String start=DateFormat.ex_strDate_to_date(startSettleDate+"","yyyy-MM-dd")+" 02:00:00";
        String endStr = DateFormat.date_ex_str(endTimeD)+" 01:59:59";
        /*String start=param.get("incomeSettleBeginTime")+" 02:00:00";
        String endStr=param.get("incomeSettleEndTime")+" 01:59:59";*/
        Date end = DateFormat.date_add_or_sub_days(DateFormat.panDuanDate(endStr), 1);
        String sql="select ifnull(sum(clear_out_amount),0)*100 clear_out_amount,ifnull(sum(clear_out_paper_amount),0)*100 clear_out_paper_amount from view_sw_st_handwork_clean_record where " +
                "LINE_CODE='"+lineCode+"' " +
                "and STATION_CODE= '"+stationCode+"' " +
                "and TVM_CODE = '"+tvmCode+"' " +
                "and CLEAN_TIME >='"+start+"' " +
                "and CLEAN_TIME <='"+DateFormat.date_ex_str(end,"yyyy-MM-dd HH:mm:ss")+"'";
        List<Map<String, Object>> maps = JdbcUtil.exeSqlQuery(sql);
        return maps;
    }
    /**
     *
     * @param tvmCheckDetail
     * @return
     */
    public static Map<String, Object> syncCashBoxBatchSystemAmount(SwStTvmCheckDetail tvmCheckDetail, JSONObject currParam,String cashBoxType)throws  Exception {
        String lineCode = tvmCheckDetail.getLineCode();
        String stationCode = tvmCheckDetail.getStationCode();
        String tvmCode =tvmCheckDetail.getTvmCode();
        String startSettleDate = currParam.getString("startTime");
        String endSettleDate = currParam.getString("endTime");
        Date endTimeD=DateFormat.stringToDate(endSettleDate);
        //利用Calendar 实现 Date日期-1天
        Calendar c = Calendar.getInstance();
        c.setTime(endTimeD);
        c.add(Calendar.DAY_OF_MONTH, -1);
        endTimeD = c.getTime();
        String startDate=DateFormat.ex_strDate_to_date(startSettleDate+"","yyyy-MM-dd");
        String endDate = DateFormat.date_ex_str(endTimeD);
        Object startTime = DateFormat.ex_strDate_to_date(startDate,"yyyyMMdd");
        Object endTime = DateFormat.ex_strDate_to_date(endDate,"yyyyMMdd");
        List<Map<String, Object>> resp = JdbcUtil.exeSqlQuery("select ifnull(sum(amount),0) amount,ifnull(sum(supplement_amount),0) supplement_amount from sc_tvm_coin_opt where station_equipment_number = '" + tvmCode + "'" +
                        //" and coin_number='"+ cashBoxCode+"'" +
                        " and cash_box_type='"+ cashBoxType+"'" +
                        " and lineid=" + lineCode+ " and station_number=" + stationCode +
                        " and opt_type = '2'"+
                        " and bookdate >='" + startTime + "' and bookdate<='" + endTime + "'"
                , "amount,supplement_amount");
        if (null != resp && 0 != resp.size()) {
            Map<String, Object> map = resp.get(0);
            return map;
        }
        return null;
    }


    /**
     * 查询分钱箱的数据
     * @param tvmCheckDetail
     * @return
     * @throws SQLException
     * @throws ParseException
     */
    public static List<Map<String, Object>> queryWillAddAuTvmBoxAmountDifference(SwStTvmCheckDetail tvmCheckDetail,JSONObject currParam, String money_box_type) throws SQLException, ParseException {
        Integer ids = tvmCheckDetail.getId();
        Object startSettleDate = currParam.get("startTime");
        Object endSettleDate = currParam.get("endTime");
        String startDate = DateFormat.ex_strDate_to_date(startSettleDate + "", "yyyy-MM-dd");
        String endDate = DateFormat.ex_strDate_to_date(endSettleDate + "", "yyyy-MM-dd");
        Object lineCode = tvmCheckDetail.getLineCode();
        Object stationCode = tvmCheckDetail.getStationCode();
        String queryCashBoxType  = "";
        //硬币回收箱
        if(money_box_type.equals("sw_st_tvm_check_detail_coin")){
            queryCashBoxType="1";
        }
        //硬币找零箱
        else if(money_box_type.equals("sw_st_tvm_check_detail_coin_change")){
            queryCashBoxType="2";
        }
        //纸币回收箱
        else if(money_box_type.equals("sw_st_tvm_check_detail_paper")){
            queryCashBoxType="3";
        }
        //纸币找零箱
        else if(money_box_type.equals("sw_st_tvm_check_detail_paper_change")){
            queryCashBoxType="4";
        }
        //废钞箱
        else if(money_box_type.equals("sw_st_tvm_check_detail_waste")){
            queryCashBoxType="5";
        }
        String sql= "select \n" +
                "0 relationid,\n" +
                "'#{startDate}' income_settle_begin_time,\n" +
                "'#{endDate}' income_settle_end_time,\n" +
                "scd.INCOME_SETTLE_ID income_settle_id,\n" +
                "scd.CASH_BOX_RELATION_ID cash_box_relation_id,\n" +
                "\tscd.LINE_CODE line_code,\n" +
                "  scd.STATION_CODE station_code,\n" +
                "  scd.TVM_CODE tvm_code,\n" +
                " "+queryCashBoxType+"  cash_box_type,\n"+
                //"scd.CASHBOX_CODE cash_box_code,\n" +
                "case scd.UNLOAD_CASE when 3 then 1 else 2 end as box_uninstall_status,\n";
        //硬币回收箱
        if(queryCashBoxType.equals("1")){
            sql+=" 0.00 system_money,\n" +
                    "0.00  bill_order_money,\n"+
                    "ifnull(vsc2.CLEAR_OUT_AMOUNT,0)*100 hand_out_money,\n" +
                    "( ifnull(scd.REAL_AMOUNT,0) + ifnull(vsc2.CLEAR_OUT_AMOUNT,0))*100 as diff_money,\n";
            //if(!lineCode.equals("1")){//如果是硬币回收 1，且线路编号不为1  ，取补币表中钱箱类型为2的补币金额,    2021-04-11调整为统一算到硬币回收箱内
            sql+="ifnull(vsc.SUPPLEMENT_AMOUNT,0)*100 supplement_money,\n"; //硬币补币
        }else if(queryCashBoxType.equals("2")){
            sql+=" 0.00 system_money,\n" +
                    " 0.00  bill_order_money,\n"+
                    "0.00 hand_out_money,\n" +
                    "ifnull(scd.REAL_AMOUNT,0)*100 diff_money,\n"+
                    //"ifnull(vsc.SUPPLEMENT_AMOUNT,0)*100 supplement_money,\n"; //硬币找零 ,所有都不算补币金额
                    "0.00 supplement_money,\n";
        }else if(queryCashBoxType.equals("3")){
            sql+="0.00 system_money,\n" +
                    "0.00  bill_order_money,\n"+
                    "ifnull(vsc2.CLEAR_OUT_PAPER_AMOUNT,0)*100 hand_out_money,\n" +
                    "( ifnull(scd.REAL_AMOUNT,0) + ifnull(vsc2.CLEAR_OUT_PAPER_AMOUNT,0))*100 as diff_money,\n" +
                    "0.00 supplement_money,\n";
        }else if(queryCashBoxType.equals("4")){
            sql+=" 0.00 system_money,\n" +
                    "0.00 bill_order_money,\n"+
                    "0.00 hand_out_money,\n" +
                    "ifnull(scd.REAL_AMOUNT,0)*100 diff_money,\n"+
                    "ifnull(vsc.SUPPLEMENT_AMOUNT,0)*100 supplement_money,\n"; //纸币找零箱补币
        }else if(queryCashBoxType.equals("5")){
            sql+= "0.00 system_money,\n" +
                    "0.00  bill_order_money,\n"+
                    "0.00 hand_out_money,\n" +
                    "ifnull(scd.REAL_AMOUNT,0)*100 diff_money,\n"+
                    "0.00 supplement_money,\n";
        }
        sql+="0 sc_sale_amount,\n" +
                "0 sc_unload_amount,\n" +
                "0 sc_hand_out_amount,\n" +
                "0 sc_supplemnt_amount,\n" +
                "ifnull(scd.SYS_AMOUNT,0)*100 unload_display_money,\n" +
                "ifnull(scd.REAL_AMOUNT,0)*100 actual_money,\n" +
                //"ifnull(vsc.SUPPLEMENT_AMOUNT,0)*100 supplement_money,\n" +
                " scd.RECORD_TIME record_time,\n" +
                "2 data_source,\n" +  //改为SC ，原来是结账单据
                "0 audit_status,\n" +
                "0 assist_invest_status,\n" +
                "'' diff_reason,\n" +
                "0 is_add_assist,\n" +
                "0 is_add_level_assist,\n" +
                "   scd.passenger_attendant_id,\n"+//定时任务新增需要
                "   scd.passenger_attendant_job_no,\n"+
                "   scd.passenger_attendant_name,\n"+
                "   scd.attendance_person_id,\n"+
                "   scd.attendance_person_job_no,\n"+
                "   scd.attendance_person_name,\n"+
                "   scd.creator_id,\n"+
                "   scd.creator_name,\n"+
                "now() create_time\n" +
                "from "+
                " (select \n" +
                "b.id sstcdId,"+
                "scd.ID INCOME_SETTLE_ID,\n" +
                "a.ID CASH_BOX_RELATION_ID,\n" +
                "\tscd.LINE_CODE,\n" +
                "  scd.STATION_CODE,\n" +
                "  scd.TVM_CODE,\n" +
                //"\ta.CASHBOX_CODE,\n" +
                "a.UNLOAD_STATUS*1 UNLOAD_STATUS,\n" +
                "a.UNLOAD_CASE UNLOAD_CASE,\n" +
                "a.SYS_AMOUNT,\n" +
                "a.REAL_AMOUNT,\n" +
                "   scd.passenger_attendant_id,\n"+//定时任务新增需要
                "   scd.passenger_attendant_job_no,\n"+
                "   scd.passenger_attendant_name,\n"+
                "   scd.attendance_person_id,\n"+
                "   scd.attendance_person_job_no,\n"+
                "   scd.attendance_person_name,\n"+
                "   scd.creator_id,\n"+
                "   scd.creator_name,\n"+
                "   scd.RECORD_TIME\n" +
                "from "+money_box_type+" a,sw_st_tvm_check_detail b,SW_ST_TVM_INCOME_SETTLE scd \n" +
                "where (scd.settle_status <>2 or scd.SETTLE_STATUS is null)\n" +
                "        \tand scd.line_code='#{lineCode}'\n" +
                "        \tand scd.station_code='#{stationCode}'\n" +
                "        \tand date_add(scd.RECORD_TIME,interval 12 hour) between '#{startSettleDate}' and '#{endSettleDate}'\n" +
                "        \tand scd.DEL_FLAG = '0'\n" +
                "        \tand scd.line_code=b.line_code and scd.station_code=b.station_code and scd.tvm_code = b.tvm_code\n" +
                "\t\t\t\t\tand date_add(b.revenue_time,interval 12 hour) between '#{startSettleDate}' and '#{endSettleDate}'\n" +
                "        \tand b.ID = a.RELATION_ID and a.del_flag = '0') scd\n" ;
        if(queryCashBoxType.equals("1")){// && !lineCode.equals("1")){//硬币回收箱
            sql+= "left join \n" +
                    "        (select LINE_CODE,STATION_CODE,TVM_CODE,TVM_CASH_BOX_TYPE, sum(SUPPLEMENT_AMOUNT) SUPPLEMENT_AMOUNT\n" +
                    "        \tfrom view_sw_st_tvm_supplement_currency \n" +
                    "\t\t\t\t\twhere SUPPLEMENT_CURRENCY_TIME between '#{startSettleDate}' and '#{endSettleDate}'  and TVM_CASH_BOX_TYPE='2'\n" +
                    "        \tgroup by line_code,station_code,tvm_code,TVM_CASH_BOX_TYPE) vsc \n"+
                    "     on scd.line_code=vsc.line_code and scd.station_code=vsc.station_code and scd.tvm_code = vsc.tvm_code  and vsc.TVM_CASH_BOX_TYPE='2' \n" ;

        }else if(queryCashBoxType.equals("2")){
        }else if(queryCashBoxType.equals("3")){

        }else if(queryCashBoxType.equals("4")){
            sql+= "left join \n" +
                    "        (select LINE_CODE,STATION_CODE,TVM_CODE,TVM_CASH_BOX_TYPE,sum(SUPPLEMENT_AMOUNT) SUPPLEMENT_AMOUNT\n" +
                    "        \tfrom view_sw_st_tvm_supplement_currency \n" +
                    "\t\t\t\t\twhere SUPPLEMENT_CURRENCY_TIME between '#{startSettleDate}' and '#{endSettleDate}' and  TVM_CASH_BOX_TYPE='4' \n" +
                    "        \tgroup by line_code,station_code,tvm_code,TVM_CASH_BOX_TYPE) vsc \n"+
                    "     on scd.line_code=vsc.line_code and scd.station_code=vsc.station_code and scd.tvm_code = vsc.tvm_code  and vsc.TVM_CASH_BOX_TYPE='4' \n" ;
        }else if(queryCashBoxType.equals("5")){

        }
        if(queryCashBoxType.equals("1")|| (queryCashBoxType.equals("3"))){//手工清出金额算到硬币回收箱和纸币回收箱内
            sql+= "left join \n" +
                    "        (select LINE_CODE,STATION_CODE,TVM_CODE,sum(CLEAR_OUT_AMOUNT) CLEAR_OUT_AMOUNT," +
                    "           sum(CLEAR_OUT_PAPER_AMOUNT) CLEAR_OUT_PAPER_AMOUNT\n" +
                    "        \tfrom view_sw_st_handwork_clean_record \n" +
                    "\t\t\t\t\twhere CLEAN_TIME between '#{startSettleDate}' and '#{endSettleDate}'\n" +
                    "        \tgroup by line_code,station_code,tvm_code) vsc2 on scd.line_code=vsc2.line_code and scd.station_code=vsc2.station_code and scd.tvm_code = vsc2.tvm_code\n" ;
        }
        sql+= "\nwhere scd.sstcdId in ("+ids+")";
        sql=sql.replace("#{startDate}",startDate).replace("#{endDate}",endDate);
        sql=sql.replace("#{startSettleDate}",startSettleDate+"").replace("#{endSettleDate}",endSettleDate+"");
        sql=sql.replace("#{lineCode}",lineCode+"").replace("#{stationCode}",stationCode+"");
        LOGGER.info("查詢分钱箱审核主表预处理SQL----"+sql);
        List<Map<String, Object>> maps = JdbcUtil.exeSqlQuery(sql);
        return maps;
    }



}
